On Jan 21, 3:38 am, Timbo wrote:
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+AG 18,
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.
On Jan 21, 3:38*am, Timbo wrote:
I have been trying to get my head around something all morning and I
just can't seem to get it to work I am usually good with embedded if
statements and the like but I just can't come up with a sum that will do
this.
In O1 I have a control which can range from 0 to 5, 0 being no weeks of
the month have passed and and 1-5 being the number of the weeks that
have passed in for the current month. Our accounting means we have five
week months occasionally.
In week 0 this is the sum -
Sum(I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF1 8+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 being
0,1,2,3,4 or 5?
I have tried If but I can't seemt get it to work and I am usually
pretty good with them.
Does anyone have any other ideas?
Thanks in advance.
--
Timbo
------------------------------------------------------------------------
Timbo's Profile:http://www.thecodecage.com/forumz/member.php?userid=24
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=52790