[Alex]

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

[Gary''s Student]

=(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

[Luke M]

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