Omitting Zero's in Formula's

[WalkerDude999]

Howdy,

I'm a heavy walker and I like to keep track of my times, distances and
speeds. I have my spreadsheet set up to look like a calendar. i.e. horizontal
rows of days. At the end of each week I have a total column set up. It
calculates, high speeds, low speeds, max distances, min distances and so
forth. The problem is that it calculates in "zero's" I don't always walk and
on those days, the spreadsheet puts zero's in the Daily boxes. These boxes
have formula's in them as well and it's impossible to eliminate the zero's in
the weekly boxes withour wrecking the formulas's. What I want to be able to
do is to go through a list and pick out numbers greater than zero to
calculate average's and high's and lows without averaging in days I didn't
walk. How do I overlook zero's?

Thank You
WalkerDude999

 

[David Biddulph]

If you do your calculations with a formula of the sort
=IF(OR(B2="",C2=""),"",C2-B2) for subtracting start time from finish time,
then if you've got blanks for the inputs it'll leave an empty cell for the
result, rather than a zero. These blank results will be ignored by
functions such as AVERAGE, MAX, & MIN.

 

[Bob Phillips]

=AVERAGE(IF(B2:H2>0,B2:H2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Craig Covey]

To simplify elimination of zero values, use the COUNTIF command embedded into
your averaging formula, i.e., =(SUM(B2:H2))/(COUNTIF(B2:H2,">0"))