| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
specify an ending row in an array formula
I use the following formula to sum every other row up to row 500.
=SUMPRODUCT((MOD(ROW(A1:A500),2)=0)*A1:A500) However, periodically rows within that range are inserted or deleted. How can make the formula dynamic to specify the last row of the array to be the row just above the formula. Example: If the formula is in row 600 then SUMPRODUCT...A1:A599 but if the fomula ends up in row 550 then SUMPRODUCT...A1:A549 Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
specify an ending row in an array formula
Try
=SUMPRODUCT((MOD(ROW(A$1:INDIRECT("A"&ROW()-1)),2)=0)*A$1:INDIRECT("A"&ROW()-1)) "Bassman62" wrote: I use the following formula to sum every other row up to row 500. =SUMPRODUCT((MOD(ROW(A1:A500),2)=0)*A1:A500) However, periodically rows within that range are inserted or deleted. How can make the formula dynamic to specify the last row of the array to be the row just above the formula. Example: If the formula is in row 600 then SUMPRODUCT...A1:A599 but if the fomula ends up in row 550 then SUMPRODUCT...A1:A549 Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
specify an ending row in an array formula
Thank you!
Works wonderfully. "Sheeloo" wrote: Try =SUMPRODUCT((MOD(ROW(A$1:INDIRECT("A"&ROW()-1)),2)=0)*A$1:INDIRECT("A"&ROW()-1)) "Bassman62" wrote: I use the following formula to sum every other row up to row 500. =SUMPRODUCT((MOD(ROW(A1:A500),2)=0)*A1:A500) However, periodically rows within that range are inserted or deleted. How can make the formula dynamic to specify the last row of the array to be the row just above the formula. Example: If the formula is in row 600 then SUMPRODUCT...A1:A599 but if the fomula ends up in row 550 then SUMPRODUCT...A1:A549 Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
specify an ending row in an array formula
Hi,
You can simply convert the range to a list. Doing so will expand the range automatically as and when you add more data in rows -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bassman62" wrote in message ... I use the following formula to sum every other row up to row 500. =SUMPRODUCT((MOD(ROW(A1:A500),2)=0)*A1:A500) However, periodically rows within that range are inserted or deleted. How can make the formula dynamic to specify the last row of the array to be the row just above the formula. Example: If the formula is in row 600 then SUMPRODUCT...A1:A599 but if the fomula ends up in row 550 then SUMPRODUCT...A1:A549 Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula ending with a letter | Excel Worksheet Functions | |||
| Ending another applicatoni | Excel Discussion (Misc queries) | |||
| Week Ending formula | Excel Discussion (Misc queries) | |||
| Need Help Ending a Nested Formula Please | Excel Discussion (Misc queries) | |||
| Ending zero | Excel Discussion (Misc queries) | |||
Linear Mode
