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
|