Re: [AI] new doubt in excel

2012-08-31 Thread Aravind Kumar
thank you Zujar sir. 
i'm amazed with your knowledge in excel.
i asked this doubt even to my officer madam today afternoon and she also has no 
clear idea. 
i will implement this logic tomorrow in bank and tell you the result. 

thanks a  lot.


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-30 Thread Zujar Shabbir Kanchwala
oops! I didn't pay attention to sort limitation in your query. Here's the
equivalent of Lookup() for unsorted data:

=Offset(J3, Match(Large(J3:J91, 4), J3:J91, 0) - 1, -8)

The Offset() function returns a value from the cell that is X rows and Y
columns away from the specified cell. The syntax is:

Offset(start_position, x, y)

where,
start_position = the cell co-ordinates of the cell to reference while
offsetting.
x = number of rows to offset - 0 for current row, positive value for below
and negative value for above.
y = number of columns to offset - 0 for current column, positive value for
right and negative value for left.

The Match() function returns the relative position of the specified item in
an array. The syntax is:

Match(value, cellRange, matchType)

where,
value = the value to be matched.
cellRange = the range of cells to look for.
matchType = the type of match - 0 means exact match, 1 means greater than
and -1 means lesser than.

Hope this solves your problem!

--
Regards,
Zujar...

An optimist laughs to forget, whereas a pessimist forgets to laugh!




-Original Message-
From: accessindia-boun...@accessindia.org.in
[mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Aravind Kumar
Sent: Thursday, August 30, 2012 8:20 PM
To: accessindia@accessindia.org.in
Subject: Re: [AI] new doubt in excel

dear Zujar, thank you. your large function coupled with lookup worked upto
some rank like 3. but after some ranks, its displaying wrong branch names
some times.
then i checked with excel help of lookup. there, one warning is given as if
numbers are not ordered correctly whether ascending or descending, lookup
may give unexpected result. 
so we have to analyse this function more deeply.


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-30 Thread Aravind Kumar
dear Zujar, thank you. your large function coupled with lookup worked upto some 
rank like 3. but after some ranks, its displaying wrong branch names some times.
then i checked with excel help of lookup. there, one warning is given as if 
numbers are not ordered correctly whether ascending or descending, lookup may 
give unexpected result. 
so we have to analyse this function more deeply.


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-29 Thread Zujar Shabbir Kanchwala
this can be achieved with the Lookup() function of Excel. Here's a formula
to get the branch name for the top depositor in the example you specified:

=Lookup(Large(J3:J91, 1), J3:J91, B3:B91)

--
Regards,
Zujar...

An optimist laughs to forget, whereas a pessimist forgets to laugh!



-Original Message-
From: accessindia-boun...@accessindia.org.in
[mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Aravind Kumar
Sent: Tuesday, August 28, 2012 7:24 PM
To: accessindia@accessindia.org.in
Subject: Re: [AI] new doubt in excel

thank you friend, the large and small functions are working and almost
satisfied my requirement. another problem i'm having.
with large, i'm able to find best 5 performing branches with datas available
from j3 to j91. 
now, i have to display the related branch names also which are available
from b3 to b91. 
so, for example, if top performing branch is in j38 with largest number and
if i need to display the related branch's name available in b38, how to
relate with formula and display?
due to this problem, i'm manually searching and copying which consumes a lot
of time. so please help me.

regards
aravind


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-29 Thread Ekinath Khedekar
It's going to help me for sure, zujarji

thanks


On 8/27/12, Zujar Shabbir Kanchwala  wrote:
> Use the Large() function of Excel. For example, the below formula
> returns the 3rd largest number in the cell range A1:A10.
>
> =LARGE(A1:A10, 3)
>
> To get the 4th largest value in the cell range B1:B100, type the below
> formula:
>
> =LARGE(B1:B100, 4)
>
> The Small() function returns the specified smallest number in the
> given array. For example, the below formula returns the 7th smallest
> value in the range P201:P500.
>
> =SMALL(P201:P500, 7)
>
> Let me know if this helps or if you need more inputs.
>
> On 8/27/12, Aravind Kumar  wrote:
>> hai friends, as subject says, i have a doubt in excel in sorting.
>> in my bank, in chennai zone, there are 86 branches. we have to prepare a
>> report showing top5 performers in deposits advances etc.
>> if we sort manually, it will happen. but among 86 branches, there are
>> divisions like new branches, AGM branches etc which i should not disturb
>> the
>> order.
>> so i have to create a separate list showing top5 performers with maximum
>> number.
>> so kindly anybody suggest me how to identify top5 big numbers using
>> formulas? i know we can use max function for getting topmost number. but
>> i
>> want second third fourth and fifth also. if we use rank function, it will
>> return the actual rank only not the actual number or related amount. so
>> please suggest me a good way.
>>
>> regards.
>>
>> aravind.
>>
>>
>> Search for old postings at:
>> http://www.mail-archive.com/accessindia@accessindia.org.in/
>>
>> To unsubscribe send a message to
>> accessindia-requ...@accessindia.org.in
>> with the subject unsubscribe.
>>
>> To change your subscription to digest mode or make any other changes,
>> please
>> visit the list home page at
>> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>>
>>
>
>
> --
> Best Regards,
> Zujar...
>
> An optimist laughs to forget, whereas a pessimist forgets to laugh!
>
>
> Search for old postings at:
> http://www.mail-archive.com/accessindia@accessindia.org.in/
>
> To unsubscribe send a message to
> accessindia-requ...@accessindia.org.in
> with the subject unsubscribe.
>
> To change your subscription to digest mode or make any other changes, please
> visit the list home page at
> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>
>


-- 
“The waves breaking on the surface draw all the attention,
but it is the current beneath the water that determines your direction.”


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-29 Thread Pranay Gadodia
Hi,
I use Excel 2003 and one of the other ways of finding the top and bottom few 
items in a database in excel would be by using auto filter in the data menu.

Place the cell pointer anywhere in the row where you have the column headers, 
and go in data menu - filter - auto filter. This puts combo boxes in each cell 
of the header row. Now go in the desired column in this header row, press Alt + 
down arrow, and select top 10. It would give a dialog box to select whether you 
want top or bottom, 10 or different numbers, and you want items or percentages. 
Press ok to get the filtered result. Copy your result with all other relevant 
columns and paste it at desired place. To remove the filter again go in the 
filtered column in the header cell, press alt + down arrow and select 'All'.

HTH,
best,

Pranay.
- Original Message - 
From: "Aravind Kumar" 
To: 
Sent: Tuesday, August 28, 2012 7:23 PM
Subject: Re: [AI] new doubt in excel


thank you friend, the large and small functions are working and almost 
satisfied my requirement. another problem i'm having.
with large, i'm able to find best 5 performing branches with datas available 
from j3 to j91. 
now, i have to display the related branch names also which are available from 
b3 to b91. 
so, for example, if top performing branch is in j38 with largest number and if 
i need to display the related branch's name available in b38, how to relate 
with formula and display?
due to this problem, i'm manually searching and copying which consumes a lot of 
time. so please help me.

regards
aravind


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in

Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-28 Thread Aravind Kumar
thank you friend, the large and small functions are working and almost 
satisfied my requirement. another problem i'm having.
with large, i'm able to find best 5 performing branches with datas available 
from j3 to j91. 
now, i have to display the related branch names also which are available from 
b3 to b91. 
so, for example, if top performing branch is in j38 with largest number and if 
i need to display the related branch's name available in b38, how to relate 
with formula and display?
due to this problem, i'm manually searching and copying which consumes a lot of 
time. so please help me.

regards
aravind


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in



Re: [AI] new doubt in excel

2012-08-27 Thread Zujar Shabbir Kanchwala
Use the Large() function of Excel. For example, the below formula
returns the 3rd largest number in the cell range A1:A10.

=LARGE(A1:A10, 3)

To get the 4th largest value in the cell range B1:B100, type the below formula:

=LARGE(B1:B100, 4)

The Small() function returns the specified smallest number in the
given array. For example, the below formula returns the 7th smallest
value in the range P201:P500.

=SMALL(P201:P500, 7)

Let me know if this helps or if you need more inputs.

On 8/27/12, Aravind Kumar  wrote:
> hai friends, as subject says, i have a doubt in excel in sorting.
> in my bank, in chennai zone, there are 86 branches. we have to prepare a
> report showing top5 performers in deposits advances etc.
> if we sort manually, it will happen. but among 86 branches, there are
> divisions like new branches, AGM branches etc which i should not disturb the
> order.
> so i have to create a separate list showing top5 performers with maximum
> number.
> so kindly anybody suggest me how to identify top5 big numbers using
> formulas? i know we can use max function for getting topmost number. but i
> want second third fourth and fifth also. if we use rank function, it will
> return the actual rank only not the actual number or related amount. so
> please suggest me a good way.
>
> regards.
>
> aravind.
>
>
> Search for old postings at:
> http://www.mail-archive.com/accessindia@accessindia.org.in/
>
> To unsubscribe send a message to
> accessindia-requ...@accessindia.org.in
> with the subject unsubscribe.
>
> To change your subscription to digest mode or make any other changes, please
> visit the list home page at
> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>
>


-- 
Best Regards,
Zujar...

An optimist laughs to forget, whereas a pessimist forgets to laugh!


Search for old postings at:
http://www.mail-archive.com/accessindia@accessindia.org.in/

To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
with the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please 
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in