Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help for Cumulative Result
I need help with a formula to calculate the cumulative result If cell a1 contains May, what formula will give me the cumulative result 15 based on the data in the table below MAY Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Cum 1 2 3 4 5 6 15 -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=503228 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help for Cumulative Result
Hi Paul, =SUM(OFFSET($A$4,0,0,1,HLOOKUP($A$1,$A$2:$L$3,2,FA LSE))) Will work assuming that: *the month to accumulate upto is in cell A1 *the months of the yr are headers in row 2, *the number of the month is in row 3 (eg Jan = 1, Feb = 2 etc, this row can be hidden). (Someone else maybe able to suggest a way of bypassing the need for this extra row), & *row 4 contains your data to sum. This works by setting the size of the sum range by basing the amount of columns to include on the month entered in A1. To make it easier for users to vary the months to include in the total I'd also create a dropdown list in cell A1. This can be done by selecting A1 [data - validation - settings], selecting "list" for the Allow box & typing "=$A$2:$L$2" (or selcting the cells that the months are in using the mouse) into the Source field. hth, Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=503228 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help for Cumulative Result
Hi Rob Thanks for this If anybody knows a way to do it without the hidden row I'd be grateful broro183 Wrote: Hi Paul, =SUM(OFFSET($A$4,0,0,1,HLOOKUP($A$1,$A$2:$L$3,2,FA LSE))) Will work assuming that: *the month to accumulate upto is in cell A1 *the months of the yr are headers in row 2, *the number of the month is in row 3 (eg Jan = 1, Feb = 2 etc, this row can be hidden). (Someone else maybe able to suggest a way of bypassing the need for this extra row), & *row 4 contains your data to sum. This works by setting the size of the sum range by basing the amount of columns to include on the month entered in A1. To make it easier for users to vary the months to include in the total I'd also create a dropdown list in cell A1. This can be done by selecting A1 [data - validation - settings], selecting "list" for the Allow box & typing "=$A$2:$L$2" (or selcting the cells that the months are in using the mouse) into the Source field. hth, Rob Brockett NZ Always learning & the best way to learn is to experience... -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=503228 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help for Cumulative Result
Hi Paul, I had another look & using the same assumptions as before except for the need for a hidden row, the following will work; =SUM(OFFSET($A$5,0,0,1,MATCH($A$1,$A$3:$L$3,0))) Hth Rob Brockett NZ Always learning & the best way to learn is to experience... Paul Sheppard Wrote: Hi Rob Thanks for this If anybody knows a way to do it without the hidden row I'd be grateful -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=503228 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help for Cumulative Result
Hi Rob Thanks for this you are a star broro183 Wrote: Hi Paul, I had another look & using the same assumptions as before except for the need for a hidden row, the following will work; =SUM(OFFSET($A$5,0,0,1,MATCH($A$1,$A$3:$L$3,0))) Hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=503228 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help for Cumulative Result
Hi Paul, No problem, thanks for the feedback. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=503228 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |