There's Got to be an Easier Way

S

Sprint54

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
 
B

Bob Phillips

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
S

Sandy Mann

Try:

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

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
B

bpeltzer

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
 
S

Sandy Mann

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top