Any Way Around 7-Nested Function Limit?

[Vigor]

First of all, I love this group & try to lurk whenever I have a free minute
since I constantly get that "Wow! I can really USE that tip..." feeling.

I am trying to calculate the average monthly sales for each item from a
table containing one item per row, with 36 columns of monthy sales history
running backwards. If the item number itself is in column A, the sales for
Jan-04 is in column B, sales for Dec-03 is in C, Nov-03 in D, and so on
going back 36 months.

The problem is that some items were introduced less than 36 months ago
(some only a couple months ago), and hence have a zero in all the earlier
columns. I thought about find/replace zero's with "" and using the
traditional "AVERAGE" function, but due to seasonal lulls, many items also
have zero's in months LATER than months that they DO have sales, so my
average is whacky. I hit the wall trying to use 36 nested "IF" statements,
so I broke it into 6 different cells, in AL3 using:

=IF(AK3>0,AVERAGE(B3:AK3),IF(AJ3>0,AVERAGE(B3:AJ3),IF(AI3>0,AVERAGE
(B3:AI3),IF(AH3>0,AVERAGE(B3:AH3),IF(AG3>0,AVERAGE(B3:AG3),IF(AF3>0,AVERAGE
(B3:AF3),IF(AE3>0,AVERAGE(B3:AE3),AM3)))))))

and then in AM3 using:

=IF(AD3>0,AVERAGE(B3:AD3),IF(AC3>0,AVERAGE(B3:AC3),IF(AB3>0,AVERAGE
(B3:AB3),IF(AA3>0,AVERAGE(B3:AA3),IF(Z3>0,AVERAGE(B3:Z3),IF(Y3>0,AVERAGE
(B3:Y3),IF(X3>0,AVERAGE(B3:X3),AN3)))))))

etc. This works (my answer ends up in AL3, and I hide AM3:AP3), but isn't
there an easier way? At least something more direct? Some ARRAY function
perhaps?

Any help would be greatly appreciated...
Thanks
Vigor

 

[Niek Otten]

Hi Vigor,

Visit

http://www.cpearson.com/excel/nested.htm

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Peo Sjoblom]

Try

=AVERAGE(IF((B3:AK3>0)*(ISNUMBER(B3:AK3)),B3:AK3))

entered with ctrl + shift & enter

 

[Vigor]

etc. This works (my answer ends up in AL3, and I hide AM3:AP3), but
isn't there an easier way? At least something more direct? Some
ARRAY function perhaps?

Any help would be greatly appreciated...
Thanks
Vigor

Thanks to all who responded! Maybe I'm pushing my luck, but I was
actually posting this for a buddy of mine and after I gave him your
answer (he thanks you as well) it seems that I didn't understand his
entire problem. Though he needs to figure the average smonthly sales, he
also must do a variety of other calculations as well, for which he needs
to identify WHICH month to start in. Specifically, a formula whose result
would be the first (from the right) non-zero month. I guess it would be
a specific cell reference to a cell in row 3 somewhere, every cell to the
right of which would be zero.

Any ideas? Also, any good reference sources for entry-level help on
arrays in general? Thanks again..
Vigor

 

[Vigor]

Try

=AVERAGE(IF((B3:AK3>0)*(ISNUMBER(B3:AK3)),B3:AK3))

entered with ctrl + shift & enter

Thanks! It works great except I get #DIV/0! errors when all the months are
zero. Any ideas on how to give a zero result in that special case?
Thanks again...
Vigor

 

[Peo Sjoblom]

You could just use a simple IF first

=IF(COUNT(B3:AK3),AVERAGE(IF((B3:AK3>0)*(ISNUMBER(B3:AK3)),B3:AK3)),"")

 

[Vigor]

Try

=AVERAGE(IF((B3:AK3>0)*(ISNUMBER(B3:AK3)),B3:AK3))

entered with ctrl + shift & enter

After a little testing, this still only averages the non-zero values. I
caught this by comparing your result with my original one that took 6
cells to accomplish. Mine averages all the cells (including the zero's
to the LEFT of the first non-zero month). It comes back to identifying
the END of the range to be averaged (see my other post). I guess I need
to understand this array function concept, and the Excel help is less
than intuitive...
Thanks again...

Thanks
Vigor

 

[Peo Sjoblom]

After a little testing, this still only averages the non-zero values. I
caught this by comparing your result with my original one that took 6
cells to accomplish. Mine averages all the cells (including the zero's
to the LEFT of the first non-zero month). It comes back to identifying
the END of the range to be averaged (see my other post). I guess I need
to understand this array function concept, and the Excel help is less
than intuitive...
Thanks again...

I got the impression from your first post that you didn't want to included
the zero values?

If you want to include zero values just use

=AVERAGE(B3:AK3)

if you want to include zero values to the left of some point use something
like

=AVERAGE(OFFSET($B$3,,,,MATCH(A3,B4:AK4,0)))

where you would match a value in A3 in row 4 and return the average of
numbers
counted from B3 and up until (and included a value in row 4)


Regards,

Peo Sjoblom

 

[Russell Michelle]

Thanks! It works great except I get #DIV/0! errors when all the months are
zero. Any ideas on how to give a zero result in that special case?

Must be due to the values.

 

[Vigor]

Try

=AVERAGE(IF((B3:AK3>0)*(ISNUMBER(B3:AK3)),B3:AK3))

entered with ctrl + shift & enter

After a little testing, this still only averages the non-zero values. I
caught this by comparing your result with my original one that took 6 cells
to accomplish. Mine averages all the cells (including the zero's to the
LEFT of the first non-zero month). It comes back to identifying the END of
the range to be averaged (see my other post).
Thanks
Vigor