nesting more than 7 functions?

[Brian]

I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L1>0,'WS2'!M1,if(K1>0,sum('WS2'!A1:K1),if(J1>0,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.

 

[JBeaucaire]

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.

 

[Brian]

You are correct in your assumption. Cells A1:L1 represent 12 months Jan- Dec
2009, cell M1 is the total and N1 is a year to date total that is linked to
WS2 which is the exact same spreadsheet however it is the 2008 schedule. N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values > $1000 and March to Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2. I hope
this gives enough information to do a formula. Thanks again for your help.

 

[Roger Govier]

Hi Brian

maybe
=SUM(WS2!$A$1:INDEX(WS2!$A1:$L1,COUNT($A1:$L1)))

 

[JBeaucaire]

Give this a try.

=SUM((INDIRECT("WS2!A"&ROW()&":"&
LOOKUP(COUNT(A2:L2),{1,2,3,4,5,6,7,8,9,10,11,12},
{"A","B","C","D","E","F","G","H","I","J","K"})&ROW())))

 

[JBeaucaire]

Or even this simplifies it a little more:

=SUM((INDIRECT("WS2!A"&ROW()&":"&
CHOOSE(COUNT(A2:L2),"A","B","C","D","E","F","G","H","I","J","K")&
ROW())))

 

[Brian]

Roger, your formula is very close. It is adding the first three columns in
WS2 but it is adding those cells even though the values of A1:C1 were 0. It
should not add any values in WS2 unless there is a value > 0 in A1 or B1, or
C1 etc.

Roger, your second formula gave the message "a value used in the formula is
of the wrong data type".

Thanks again for trying to help with this.

 

[Roger Govier]

Hi Brian

then this should do it
=SUM(ws2!$A$1:INDEX(ws2!$A1:$L1,COUNTIF($A1:$L1,">"&0)))

Not sure what you mean by my second formula. I only posted one.
--
Regards
Roger Govier

Roger, your formula is very close. It is adding the first three columns in
WS2 but it is adding those cells even though the values of A1:C1 were 0.
It
should not add any values in WS2 unless there is a value > 0 in A1 or B1,
or
C1 etc.

Roger, your second formula gave the message "a value used in the formula
is
of the wrong data type".

Thanks again for trying to help with this.

 

[Brian]

Roger,
As it turns out your latest formula now adds all months (Jan-Dec) in WS2
even if the value in each of the cells in (A1:L1) are 0. I think you are very
close to getting this one. I appreciate your help again.

 

[Roger Govier]

Hi Brian

I am confused.
At first I thought the cells in row 1 of your sheet would be empty if the
month hadn't been reached.
That is why I first used Count.
Obviously, there must be a formula in there returning 0 until the month is
reached.

The present formula is counting whether cells in A1:L1 are greater than 0.
What is in those cells. It is a very small value which is being displayed as
0?

What result do you get for
=COUNTIF(A1:L1,">"&0)
and what do you get for
=COUNTIF(A1:L1,">"&0.000000000000001)

There must be something in those cells for the formula to include all 12
cells from WS2

 

[Brian]

Roger,

I have entered the number $0 in all cells in row 1 so we are starting with a
cell with the value $0 and not an empty cell. Both of your latest
suggestions give the same value. Is it possible that I can email you the
spreadsheet? Thanks again.

 

[Roger Govier]

Yes.
roger at technology4u dot co dot uk
Change the at and dots to make valid address

--
Regards
Roger Govier

Roger,

I have entered the number $0 in all cells in row 1 so we are starting with
a
cell with the value $0 and not an empty cell. Both of your latest
suggestions give the same value. Is it possible that I can email you the
spreadsheet? Thanks again.