How to average random cells with out counting zero?
I am trying to average every 8th cell in a column, however I do not want to
average zeros, =AVERAGE((IF(A1=0,"",A1)),(IF(A8=0,"",A8)),(IF(A16 =0,"",A16)),(IF(A24=0,"",A24))) is what I came up with but it gives the "#value" error! Please help |
How to average random cells with out counting zero?
=(A1+A8+A16+A24)/((A1<0)+(A8<0)+(A16<0)+(A24<0)) -- Gary''s Student - gsnu200850 "Alex" wrote: I am trying to average every 8th cell in a column, however I do not want to average zeros, =AVERAGE((IF(A1=0,"",A1)),(IF(A8=0,"",A8)),(IF(A16 =0,"",A16)),(IF(A24=0,"",A24))) is what I came up with but it gives the "#value" error! Please help |
How to average random cells with out counting zero?
Note that by concatenating something onto end, your cell contents are treated
as text, so to use them in further calculations will reuire you to strip off the end, and then reconvert to number (VALUE function). But, to do what you ask: =TEXT(ROUND(D31/E31,2),"#.00")&" : 1" The use of text forces the formatting of calculated value (similar to using a custom format). The #.00 format forces a display of 2 digits past the decimal, and then concatentating it to something else creates a text value (which you can't use in math operations) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Alex" wrote: I am trying to average every 8th cell in a column, however I do not want to average zeros, =AVERAGE((IF(A1=0,"",A1)),(IF(A8=0,"",A8)),(IF(A16 =0,"",A16)),(IF(A24=0,"",A24))) is what I came up with but it gives the "#value" error! Please help |
All times are GMT +1. The time now is 04:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com