Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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 | |||
Using the LEFT formula for multiple cells | Excel Worksheet Functions | |||
Use of Find with Left, Mid, Right functions in nested IF(and('s | Excel Discussion (Misc queries) | |||
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 |