software

How could this formula be simplified?
=IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=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)

quot;Sprint54quot; gt; wrote in message
...
gt; How could this formula be simplified?
gt;
=IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SU
M(K13:N13),IF($D$2gt;=5,SUM(K13:O13),IF($D$2gt;=6,SUM( K13:P13),0))))))
gt;
gt; It's purpose is to total monthly numbers for a YTD total based on the
gt; current month. In cell D2 is the current month number (1 for Jan, 2 for
Feb,
gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt; formula is entered in cell I13. The idea is that each new month the
monthly
gt; indicator in cell D2 is updated to reflect the current month. I then want
gt; the new YTD number to be summed in cell I13. Thanks
Try:

=SUM(INDIRECT(quot;K13:quot;amp;CHAR(74 $D$2)amp;quot;13quot;))

--
HTH

Sandy
with @tiscali.co.ukquot;Sprint54quot; gt; wrote in message
...
gt; How could this formula be simplified?
gt; =IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=6,SUM(K13:P13),0))))))
gt;
gt; It's purpose is to total monthly numbers for a YTD total based on the
gt; current month. In cell D2 is the current month number (1 for Jan, 2 for
gt; Feb,
gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt; formula is entered in cell I13. The idea is that each new month the
gt; monthly
gt; indicator in cell D2 is updated to reflect the current month. I then want
gt; the new YTD number to be summed in cell I13. Thanks
As it stands, this won't work after February, since the gt;=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

quot;Sprint54quot; wrote:

gt; How could this formula be simplified?
gt; =IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=6,SUM(K13:P13),0))))))
gt;
gt; It's purpose is to total monthly numbers for a YTD total based on the
gt; current month. In cell D2 is the current month number (1 for Jan, 2 for Feb,
gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt; formula is entered in cell I13. The idea is that each new month the monthly
gt; indicator in cell D2 is updated to reflect the current month. I then want
gt; the new YTD number to be summed in cell I13. Thanks

try this idea

=SUM(INDIRECT(quot;k13:quot;amp;CHAR(10 d2 64)amp;quot;13quot;))

--
Don Guillett
SalesAid Software

quot;Sprint54quot; gt; wrote in message
...
gt; How could this formula be simplified?
gt; =IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=6,SUM(K13:P13),0))))))
gt;
gt; It's purpose is to total monthly numbers for a YTD total based on the
gt; current month. In cell D2 is the current month number (1 for Jan, 2 for
gt; Feb,
gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt; formula is entered in cell I13. The idea is that each new month the
gt; monthly
gt; indicator in cell D2 is updated to reflect the current month. I then want
gt; the new YTD number to be summed in cell I13. Thanks
Thank you.. I used the offset formula.

quot;Sprint54quot; wrote:

gt; How could this formula be simplified?
gt; =IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=6,SUM(K13:P13),0))))))
gt;
gt; It's purpose is to total monthly numbers for a YTD total based on the
gt; current month. In cell D2 is the current month number (1 for Jan, 2 for Feb,
gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt; formula is entered in cell I13. The idea is that each new month the monthly
gt; indicator in cell D2 is updated to reflect the current month. I then want
gt; 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 lt;ggt;

--
Regards

Sandy
with @tiscali.co.uk

quot;Don Guillettquot; gt; wrote in message
...
gt; try this idea
gt;
gt; =SUM(INDIRECT(quot;k13:quot;amp;CHAR(10 d2 64)amp;quot;13quot;))
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Sprint54quot; gt; wrote in message
gt; ...
gt;gt; How could this formula be simplified?
gt;gt; =IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=6,SUM(K13:P13),0))))))
gt;gt;
gt;gt; It's purpose is to total monthly numbers for a YTD total based on the
gt;gt; current month. In cell D2 is the current month number (1 for Jan, 2 for
gt;gt; Feb,
gt;gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt;gt; formula is entered in cell I13. The idea is that each new month the
gt;gt; monthly
gt;gt; indicator in cell D2 is updated to reflect the current month. I then
gt;gt; want
gt;gt; the new YTD number to be summed in cell I13. Thanks
gt;
gt;
I like to show other ways to quot;skin the catquot; sometimes

--
Don Guillett
SalesAid Software

quot;Sandy Mannquot; gt; wrote in message
...
gt; Don,
gt;
gt; I kicked myself when I saw Bob's offset formula but now that I see that
gt; you were thinking along the same lines as me I feel better lt;ggt;
gt;
gt; --
gt; Regards
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt; quot;Don Guillettquot; gt; wrote in message
gt; ...
gt;gt; try this idea
gt;gt;
gt;gt; =SUM(INDIRECT(quot;k13:quot;amp;CHAR(10 d2 64)amp;quot;13quot;))
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Sprint54quot; gt; wrote in message
gt;gt; ...
gt;gt;gt; How could this formula be simplified?
gt;gt;gt; =IF($D$2=1,K13,IF($D$2gt;=2,SUM(K13:L13),IF($D$2gt;=3, SUM(K13:M13),IF($D$2gt;=4,SUM(K13:N13),IF($D$2gt;=5,SU M(K13:O13),IF($D$2gt;=6,SUM(K13:P13),0))))))
gt;gt;gt;
gt;gt;gt; It's purpose is to total monthly numbers for a YTD total based on the
gt;gt;gt; current month. In cell D2 is the current month number (1 for Jan, 2 for
gt;gt;gt; Feb,
gt;gt;gt; etc..) The monthly actuals are located in cells K13 through V13. The
gt;gt;gt; formula is entered in cell I13. The idea is that each new month the
gt;gt;gt; monthly
gt;gt;gt; indicator in cell D2 is updated to reflect the current month. I then
gt;gt;gt; want
gt;gt;gt; the new YTD number to be summed in cell I13. Thanks
gt;gt;
gt;gt;
gt;
gt;