Re: [AI] new doubt in excel
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
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
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
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
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
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
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
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
