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,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

=SUM(OFFSET(K13,,,1,D2))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Sprint54" <[email protected]> wrote in message
news:[email protected]...
> 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

Try:

=SUM(INDIRECT("K13:"&CHAR(74+$D$2)&"13"))

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk


"Sprint54" <[email protected]> wrote in message
news:[email protected]...
> 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,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

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,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

try this idea

=SUM(INDIRECT("k13:"&CHAR(10+d2+64)&"13"))

--
Don Guillett
SalesAid Software
[email protected]
"Sprint54" <[email protected]> wrote in message
news:[email protected]...
> 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,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

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,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

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
[email protected]
[email protected] with @tiscali.co.uk

"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> try this idea
>
> =SUM(INDIRECT("k13:"&CHAR(10+d2+64)&"13"))
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Sprint54" <[email protected]> wrote in message
> news:[email protected]...
>> 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,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
>
>

I like to show other ways to "skin the cat" sometimes

--
Don Guillett
SalesAid Software
[email protected]
"Sandy Mann" <[email protected]> wrote in message
news:%[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
>> try this idea
>>
>> =SUM(INDIRECT("k13:"&CHAR(10+d2+64)&"13"))
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Sprint54" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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,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
>>
>>
>
>