Embedded if?

[Timbo]

I have been trying to get my head around something all morning and
just can't seem to get it to work I am usually good with embedded i
statements and the like but I just can't come up with a sum that will d
this.

In O1 I have a control which can range from 0 to 5, 0 being no weeks o
the month have passed and and 1-5 being the number of the weeks tha
have passed in for the current month. Our accounting means we have fiv
week months occasionally.

In week 0 this is the sum
Sum(I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF18+AG18)

For week 1 the sum is SUM(I18+J18+V18+W18+X18+Y18,AC18)

For week 2 the sum is SUM(I18+J18+W18+X18+Y18+AC18+AD18)

For week 3 the sum is SUM(I18+J18+X18+Y18+AC18+AD18+AE18)

For week 4 the sum is SUM(I18+J18+Y18+AC18+AD18+AE18+AF18)

And for week 5 the sum is SUM(I18+J18+AC18+AD18+AE18+AF18+AG18)

Can I put all of that into one sum dependent on the value in 01 bein
0,1,2,3,4 or 5?

I have tried If but I can't seemt get it to work and I am usuall
pretty good with them.

Does anyone have any other ideas?

Thanks in advance

 

[Stephen Lloyd]

Replace A2 with whichever cell you keep the week number in.

=IF(A2=0,SUM(I18:J18,U18:Y18,AC18:AG18),
IF(A2=1,SUM(I18:J18,V18:Y18,AC18),
IF(A2=2,SUM(I18:J18,W18:Y18,AC18:AD18),
IF(A2=3,SUM(I18:J18,X18:Y18,AC18:AE18),
IF(A2=4,SUM(I18:J18,Y18,AC18:AF18),
IF(A2=5,SUM(I18:J18,AC18:AG18),"Not Valid"))))))

 

[joeu2004]

Can I put all of that into one sum dependent on the value
in 01 being 0,1,2,3,4 or 5?

At a minimum, take a look at the CHOOSE function. (Also, I see no
point in your using the SUM function per se.) In your case, you would
write:

=choose(O1+1,
I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF18+AG18,
I18+J18+V18+W18+X18+Y18+AC18, ...etc...)

But I see some opportunity for simplication. At a mimimum, all of the
sums include I18+J18+AC18. So you could take out the common term.
For example:

=I18+J18+AC18
+ choose(O1+1, U18+V18+W18+X18+Y18+AD18+AE18+AF18+AG18,
V18+W18+X18+Y18, ...etc...)

Other manipulations come to mind, but they are only half-baked at the
moment. Not sure they are worth the effort. For example, a clever
use of SUM and OFFSET might simplify the two subexpressions involving
the sums of variable subsets of U18:Y18 and AC18:AG18.