|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 24th, 2005, 09:54 PM
|
|||
|
|||
An array formula to sum row max
I have a data range of 1000 rows and 20 columns. I need a sum of the maximum number of each rows. I usually add another column to calculate row max. But is it possible to eliminate this intermediate step and just use a single cell array formula to return the sum of row maxes? Thanks -- ucamms ------------------------------------------------------------------------ ucamms's Profile: http://www.excelforum.com/member.php...o&userid=19151 View this thread: http://www.excelforum.com/showthread...hreadid=382107 
|
|
#2
June 24th, 2005, 10:36 PM
|
|||
|
|||
|
Assuming that A1:T1000 contains your data, try... =SUMPRODUCT(SUBTOTAL(4,OFFSET(A1:T1000,ROW(A1:T100 0)-MIN(ROW(A1:T1000)),0,1))) Hope this helps! ucamms Wrote: I have a data range of 1000 rows and 20 columns. I need a sum of the maximum number of each rows. I usually add another column to calculate row max. But is it possible to eliminate this intermediate step and just use a single cell array formula to return the sum of row maxes? Thanks -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=382107
|
|
#3
June 27th, 2005, 08:54 PM
|
|||
|
|||
|
It wooked. Very clever. I didn't think of using offset() and row() combination. Thank you for helping. -- ucamms ------------------------------------------------------------------------ ucamms's Profile: http://www.excelforum.com/member.php...o&userid=19151 View this thread: http://www.excelforum.com/showthread...hreadid=382107
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Array Formula, noncontigous range | Werner Rohrmoser | Worksheet Functions | 1 | June 22nd, 2005 12:11 PM |
| max/if array formula question | Mparekh | Worksheet Functions | 1 | October 4th, 2004 10:03 PM |
| Question on Array Formula | John | Worksheet Functions | 1 | February 21st, 2004 05:40 PM |
| Array Formula - Use of OFFSET function with array argument | Alan | Worksheet Functions | 2 | February 11th, 2004 09:38 PM |
| Funcs work differently as array formula? | Jonathan Rynd | Worksheet Functions | 3 | January 22nd, 2004 03:19 AM |
Linear Mode
