Remember Me? December 21st 07, 04:32 AM posted to microsoft.public.excel.worksheet.functions
 Iany external usenet poster First recorded activity by ExcelBanter: Dec 2007 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 December 21st 07, 04:57 AM posted to microsoft.public.excel.worksheet.functions
 yshridhar external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 229 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 December 21st 07, 05:26 AM posted to microsoft.public.excel.worksheet.functions
 yshridhar external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 229 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 December 21st 07, 06:41 AM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221 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 December 21st 07, 11:51 PM posted to microsoft.public.excel.worksheet.functions
 RagDyeR external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,572 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 December 22nd 07, 12:32 AM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post MJW[_2_] Excel Discussion (Misc queries) 8 September 20th 07 09:22 PM Emily G Excel Worksheet Functions 2 April 11th 07 01:59 AM CAPTGNVR Excel Discussion (Misc queries) 10 February 14th 07 11:16 PM Intotao Excel Worksheet Functions 1 January 9th 06 09:33 PM Alice Excel Worksheet Functions 3 January 14th 05 10:17 PM

All times are GMT +1. The time now is 05:32 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd. Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017