|
There's Got to be an Easier Way
How could this formula be simplified?
=IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SUM(K13:N13),IF($D$2=5,SU M(K13:O13),IF($D$2=6,SUM(K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
There's Got to be an Easier Way
=SUM(OFFSET(K13,,,1,D2))
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Sprint54" wrote in message ... How could this formula be simplified? =IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SU M(K13:N13),IF($D$2=5,SUM(K13:O13),IF($D$2=6,SUM( K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
There's Got to be an Easier Way
As it stands, this won't work after February, since the =2 condition would
be satisfied. And you'll soon hit Excel's limit of seven levels of nested functions. I think you can replace it with =sum(offset($k$13,0,0,1,$d$2)) --Bruce "Sprint54" wrote: How could this formula be simplified? =IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SUM(K13:N13),IF($D$2=5,SU M(K13:O13),IF($D$2=6,SUM(K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
There's Got to be an Easier Way
try this idea
=SUM(INDIRECT("k13:"&CHAR(10+d2+64)&"13")) -- Don Guillett SalesAid Software "Sprint54" wrote in message ... How could this formula be simplified? =IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SUM(K13:N13),IF($D$2=5,SU M(K13:O13),IF($D$2=6,SUM(K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
There's Got to be an Easier Way
Thank you.. I used the offset formula.
"Sprint54" wrote: How could this formula be simplified? =IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SUM(K13:N13),IF($D$2=5,SU M(K13:O13),IF($D$2=6,SUM(K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
There's Got to be an Easier Way
Don,
I kicked myself when I saw Bob's offset formula but now that I see that you were thinking along the same lines as me I feel better <g -- Regards Sandy with @tiscali.co.uk "Don Guillett" wrote in message ... try this idea =SUM(INDIRECT("k13:"&CHAR(10+d2+64)&"13")) -- Don Guillett SalesAid Software "Sprint54" wrote in message ... How could this formula be simplified? =IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SUM(K13:N13),IF($D$2=5,SU M(K13:O13),IF($D$2=6,SUM(K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
There's Got to be an Easier Way
I like to show other ways to "skin the cat" sometimes
-- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... Don, I kicked myself when I saw Bob's offset formula but now that I see that you were thinking along the same lines as me I feel better <g -- Regards Sandy with @tiscali.co.uk "Don Guillett" wrote in message ... try this idea =SUM(INDIRECT("k13:"&CHAR(10+d2+64)&"13")) -- Don Guillett SalesAid Software "Sprint54" wrote in message ... How could this formula be simplified? =IF($D$2=1,K13,IF($D$2=2,SUM(K13:L13),IF($D$2=3, SUM(K13:M13),IF($D$2=4,SUM(K13:N13),IF($D$2=5,SU M(K13:O13),IF($D$2=6,SUM(K13:P13),0)))))) It's purpose is to total monthly numbers for a YTD total based on the current month. In cell D2 is the current month number (1 for Jan, 2 for Feb, etc..) The monthly actuals are located in cells K13 through V13. The formula is entered in cell I13. The idea is that each new month the monthly indicator in cell D2 is updated to reflect the current month. I then want the new YTD number to be summed in cell I13. Thanks |
| All times are GMT +1. The time now is 03:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com