Hello everyone, for example if I were to have a client name and the amount the client paid in two columns. How could I find the maximum amount paid and also the display the client with the maximum without going back into my database, which is huge.
If you have any help or if I can describe my problem a littler clearer, please reply.
Thanks for looking!
Scott
You should be able to pull out the MAX value from Amount column:
=MAX(Sheet1!B:B)
Let's say you put that in sheet2 cell B2. Then in A2 you could put this formula to pull back the matching client name:
=INDEX(Sheet1!A:A, MATCH(B2, Sheet1!B:B, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
alright thank you. I'll give it a try and let you know how it goes
Okay it worked mostly perfect. However, I have another question. If there are multiple clients with the same amount, how could I get that equation to yield multiple records?
You can't, that's a whole different can of worms. This will be a magnitude of complexity increase. Let's move up to row 1 for this.
B1: =MAX(Sheet1!B:B)
C1: =COUNTIF(Sheet1!B:B, B1) (tells you how many rows have the value)
First formula
A1: =INDEX(Sheet1!A:A, MATCH(B1, Sheet1!B:B, 0))
Second formula, copied down:
A2: =IF(COUNTA($A$1:$A1)>=$C$1, "", INDEX(OFFSET(INDEX(Sheet1!A:A, MATCH(A1, Sheet1!A:A, 0)), 1, , COUNTA(Sheet1!A:A), ), MATCH($B$1, OFFSET(INDEX(Sheet1!A:A, MATCH(A1, Sheet1!A:A, 0)), 1, 1, COUNTA(Sheet1!A:A), ), 0)))
oh dang. Well let me give it a whirl. There's a good chance I'll be back with another question. Thank you though!
okay, I think I'm close. However, I used my client/total as just an example so my cells are little different. I understand the jist of it but I was wondering if you could give me a quick debrief of what the OFFSET equation is because I saw it was asking for columns and rows but didn't know which ones to use on my spreadsheet.
I guess to make this easy
Your A1 (index from part 1)= A10 for me along with
A2 (2nd formula)= A11
B1 (max)= J219 on sheet 4 but I also have a referenced cell to sheet 1 in C10 but J219 is the original max going through all of the data
C1= (count of max)= J220 on sheet 4
I don't know if this helps at all but I think the OFFSET equation is the reason I got an error message
If this provides any sort of clarity at all.
=IF(COUNTA('Sheet4'!$G$4:$G$218)>='Sheet1!$E$10,"",INDEX(OFFSET(INDEX('Sheet4'!G4:G218,MATCH('Sheet1'!A10,'Sheet4!G4:G218,0)),1,,COUNTA('Sheet4!G4:G218),),MATCH('Sheet1'!$D$10,OFFSET(INDEX('Sheet4'!G4:G218,MATCH('Sheet1'!A10,'Sheet4'!G4:G218,0)),1,1,COUNTA('Sheet4'!G4:G218),),0)))
G4:G18 on Sheet4 is the data that contains the client names and I entered this whole equation into cell A11 on Sheet1
Last edited by ScottBeatty; 06-19-2013 at 12:16 PM.
OFFSET(position, moverow(s), movecolumn(s), [height of range], [width of range])
This formula lets you take a starting position, move however many rows and columns, then adjust the height/width of the resulting position to create a new "range".
I'm using it to find the position of the first client that was matched, offset down a row to exclude everything above, and create a new range of just the cells below, then we run a new INDEX/MATCH on the new range down below. This should give us the second match. Then we repeat below the second match, ad nauseum.
So maybe:
=IF(COUNTA($A$10:$A10)>=$E$10, "", INDEX(OFFSET(INDEX(Sheet4!$G$4:$G$218, MATCH(Sheet1!A10, Sheet4!$G$4:$G$218, 0)), 1, , COUNTA(Sheet4!$G$4:$G$218) , ), MATCH(Sheet1!$D$10, OFFSET(INDEX(Sheet4!$G$4:$G$218, MATCH(A10, Sheet4!$G$4:$G$218, 0)), 1, 1, COUNTA(Sheet4!$G$4:$G$218), ), 0)))
Well of course after entering everything, I got a value of #N/A.
You can use the Evaluate Formula feature on the Formulas tab to watch the formula unfold one calc at a time, should help you spot the problem quickly.
Okay so a few hours later and I still haven't figured out the problem, so I was wondering if you had any suggestions.
Through the evaluate formula process:
Original =IF(COUNTA($A$12:$A12)>=$B$17,"",INDEX(OFFSET(INDEX(Sheet4!$G$4:$G$218,MATCH(Sheet1!$A$12,Sheet4!$G$4:$G$218,0)),1,,COUNTA(Sheet4!$G$4:$G$218),),MATCH(Sheet1!$B$16,OFFSET(INDEX(Sheet4!$G$4:$G$218,MATCH(Sheet1!A12,Sheet4!G4:G218,0)),1,1,COUNTA(Sheet4!$G$4:$G$218),),0)))
Reduces to =IF (FALSE, #N/A, INDEX (Sheet4!$G$6:$G$220, MATCH(.5, Sheet4!$H$6: $H$220, 0)))
(this #N/A is because the statement is not true)
And then finally, =IF (FALSE, #N/A, INDEX(Sheet4! $G$6: $G$220, #N/A))
Because it is matching a percent I have, or I want to solve for in the wrong column. Rather than looking in column H, it should be looking in J with the other values of its kind but I cannot figure out how to fix this.
Last edited by JBeaucaire; 06-19-2013 at 05:29 PM.
I made a quick fix. I just cut the column and moved it over. Worked like a charm. Thank you for all of your help today!
Remember this:
OFFSET(position, moverow(s), movecolumn(s), [height of range], [width of range])
In your formula above, you see a 1 for that second parameter, meaning move one column to the right, from G to H. Change that to a 3 to move over to column J.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.
Does that long equation only work if you wanted to find one duplicate of the same max or will it work for any amount of the occurences of the max?
Or better yet, is there an equation to enter the top 5? I know a way to sort but I was wondering if there was an equation that would yield the same results on a different page
ah I figured it out and it's a little cleaner than the long formula. I think I'll just stick with this Top 10 instead of just counting the number of maximums.
It should work for the number of cells you copy down into. So if you copy formula2 down 5 times, you'd get a total of 6 results (including formula1).Originally Posted by ScottBeatty
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks