Any Way Around 7-Nested Function Limit?

V

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
 
P

Peo Sjoblom

Try

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

entered with ctrl + shift & enter
 
V

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
 
V

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
 
P

Peo Sjoblom

You could just use a simple IF first

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

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
 
P

Peo Sjoblom

Vigor said:
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
 
V

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
 

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

Top