Home 
Search 
Today's Posts 
#1




How do I find max then sum cells above & to the left?
If
A1 = 1 B1 = 2 C1 = 3 A2 = 4 B2 = 5 C2 = 6 A3 = 9 B3 = 8 C3 = 7 How do I find the maximum value in row 3 and then sum the numbers in rows 1 and 2 above and to the right of the maximum value in row 3? I have tried the formula =SUM(OFFSET(MAX(A3:C3),2,0,2,3)) but Excel doesn't appear to accept the maximum function within the offset function. Thanks in advance. Ian 
#2




How do I find max then sum cells above & to the left?
Try the following formula in A4, B4
=SUMIF(A$1:A$3,""&MAX($C$1:$C$3),A$1:A$3) with regards Sridhar "Iany" wrote: If A1 = 1 B1 = 2 C1 = 3 A2 = 4 B2 = 5 C2 = 6 A3 = 9 B3 = 8 C3 = 7 How do I find the maximum value in row 3 and then sum the numbers in rows 1 and 2 above and to the right of the maximum value in row 3? I have tried the formula =SUM(OFFSET(MAX(A3:C3),2,0,2,3)) but Excel doesn't appear to accept the maximum function within the offset function. Thanks in advance. Ian 
#3




How do I find max then sum cells above & to the left?
sorry i couldnot understand the question.
My mistake You can try it in the following way In A4 = ADDRESS(3,MATCH(MAX(A3:C3),A3:C3,0)) a5 = =SUM(OFFSET(indirect(a4),2,0,2,3)) with regards Sridhar "Iany" wrote: If A1 = 1 B1 = 2 C1 = 3 A2 = 4 B2 = 5 C2 = 6 A3 = 9 B3 = 8 C3 = 7 How do I find the maximum value in row 3 and then sum the numbers in rows 1 and 2 above and to the right of the maximum value in row 3? I have tried the formula =SUM(OFFSET(MAX(A3:C3),2,0,2,3)) but Excel doesn't appear to accept the maximum function within the offset function. Thanks in advance. Ian 
#4




How do I find max then sum cells above & to the left?
Perhaps more generic, placed in say, A5:
=SUM(OFFSET(IV1:IV2,,,,MATCH(MAX(3:3),3:3,0)257))  Max Singapore http://savefile.com/projects/236895 xdemechanik  "Iany" wrote: If A1 = 1 B1 = 2 C1 = 3 A2 = 4 B2 = 5 C2 = 6 A3 = 9 B3 = 8 C3 = 7 How do I find the maximum value in row 3 and then sum the numbers in rows 1 and 2 above and to the right of the maximum value in row 3? I have tried the formula =SUM(OFFSET(MAX(A3:C3),2,0,2,3)) but Excel doesn't appear to accept the maximum function within the offset function. Thanks in advance. Ian 
#5




How do I find max then sum cells above & to the left?
Try this:
=SUM(INDEX(A1:C1,MATCH(MAX(A3:C3),A3:C3,0)+1):INDE X(A2:C2,MATCH(MAX(A3:C3),A3:C3,0)+1)) You must realize though, that if the value 9 (highest) was in C3 instead of A3, you'd get referenced to a cell outside your posted range, and that would return a #REF! error.  HTH, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Iany" wrote in message news If A1 = 1 B1 = 2 C1 = 3 A2 = 4 B2 = 5 C2 = 6 A3 = 9 B3 = 8 C3 = 7 How do I find the maximum value in row 3 and then sum the numbers in rows 1 and 2 above and to the right of the maximum value in row 3? I have tried the formula =SUM(OFFSET(MAX(A3:C3),2,0,2,3)) but Excel doesn't appear to accept the maximum function within the offset function. Thanks in advance. Ian 
#6




How do I find max then sum cells above & to the left?
Perhaps this:
=SUMPRODUCT((COLUMN(A1:C2)=MATCH(MAX(A3:C3),A3:C3 ,0))*A1:C2) Is that something you can work with?  Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Iany" wrote in message news If A1 = 1 B1 = 2 C1 = 3 A2 = 4 B2 = 5 C2 = 6 A3 = 9 B3 = 8 C3 = 7 How do I find the maximum value in row 3 and then sum the numbers in rows 1 and 2 above and to the right of the maximum value in row 3? I have tried the formula =SUM(OFFSET(MAX(A3:C3),2,0,2,3)) but Excel doesn't appear to accept the maximum function within the offset function. Thanks in advance. Ian 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Use of Find with Left, Mid, Right functions in nested IF(and('s  Excel Discussion (Misc queries)  
Using the LEFT formula for multiple cells  Excel Worksheet Functions  
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER  Excel Discussion (Misc queries)  
Find LARGE, and th cell 3 cells to the left?  Excel Worksheet Functions  
FIND or LEFT or MID to swap first name with last name?  Excel Worksheet Functions 