Omitting Zero's in Formula's
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 |
Omitting Zero's in Formula's
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. -- David Biddulph "WalkerDude999" wrote in message ... 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 |
Omitting Zero's in Formula's
=AVERAGE(IF(B2:H20,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) "WalkerDude999" wrote in message ... 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 |
Omitting Zero's in Formula's
To simplify elimination of zero values, use the COUNTIF command embedded into
your averaging formula, i.e., =(SUM(B2:H2))/(COUNTIF(B2:H2,"0")) "David Biddulph" wrote: 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. -- David Biddulph "WalkerDude999" wrote in message ... 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 |
All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com