• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to average a list of numbers and exclude 0

A

amarkbass

New Member
Hello, I've gotten a lot of great info on this site, I figured that I'd ask my question here before anywhere else --- this is my first post.


I'm trying to reduce the amount of manual selecting/deselecting of cells somehow. Any help would be greatly appreciated.


I have a range of products and witihin each group, corresponding sub-products. These products/sub-products have been sold over the course of some-odd number of weeks over the course of the product life cycle.


Some sub-products sell every week, others do not. My data is organized such that in order to calculate the average suggested selling price for all products for a particular week (listed in rows) I must select each cell and seperate it by commas rather selecting the range from begining to end seperated by commas.


Right now I'm starting with:


=SUBTOTAL(1,L29,T29,AB29,AJ29,AR29,AZ29,BH29,BP29,BX29,CF29,CN29,CV29,DD29,DL29,DT29,EB29,EJ29,ER29,EZ29,FH29,FP29,FX29,GF29)


And after deselecting the cells that contain a '0' value, I end up with:

=SUBTOTAL(1,L29,AJ29,AR29,AZ29,BH29,BP29,BX29,CF29,DD29,DT29,EB29,ER29,EZ29,FH29,FP29,FX29)


weeks are listed by row

columns include each sub-product and are then broken down by volume, cost, price1, price 2, margin, etc


Is anyone aware of a way to exclude the cells that equal zero in the list so that I won't have to go in and 'deselect' the zero values for each week? The zero values are the only ones that I wish to exclude in the average calculation, all others can remain.


Best regards,


AMB
 
in your formula of average the denominator can be like this:


= countif ( range of cells, ">0")
 
=AVERAGEIF(A1:A20,">0")

or

=AVERAGEIFS(A1:A20,A1:A20,">0")
 
Thank you for the responses fred and Hui.


If you'll notice, the values listed in the first subtotal calculation are not a range of numbers (ie: =SUBTOTAL(1,L29:GF29).


Each category is seperated by about 7 columns. Each subproduct includes the same 7 column headings: volume, cost, price 1, price 2, revenue, cost 2, and inventory which is why I cannot use a range for this calculation.


Maybe this is one of those manual issues that might not be a good fit for excel.

BTW I'm using Excel 2003.
 
The key is knowing the pattern of how far apart each cell is. Try this array formula:


=AVERAGE(IF((MOD(COLUMN(L25:FX25)-12,8)=0)*(L25:FX25>0),L25:FX25))


Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter

The MOD function is there to force formula to look at every 8th column. You're starting in column L (the 12th column), hence the "-12" bit.
 
Incredible Luke M!


I'm currently using a CSE/array formula for another sheet using a combination of index, small, if, row to give me a kind of 'continuous vlookup' deal... I'll try this one out and let you know how it goes.


Even if it doesn't work, I'd love to find out more about this MOD function..
 
Cool beans.

MOD function explained...

Syntax: MOD(Number, Divisor)

MOD takes the Number, divides by divisor, and returns the remainder. E.g, MOD(3,2) = 1, MOD(5,3) = 2

Commonly used in "zebra striping" if you don't want to install Analysis ToolPak and use the ISEVEN or ISODD functions

Find even rows:

=MOD(ROW(),2)=0


Let us know if there's anything else we can do to help!
 
Luke M - I'm sure you've heard this many times before, but you are the MAN/a genius!

Thanks a ton!


I hope to be able to answer an excel question for someone in the future just as you've helped me...


AMB
 
You must log in or register to reply here.
Back
Top