Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is Paste Formulas option is acting like Paste All option? | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
locking formulas?? | Excel Discussion (Misc queries) | |||
IF formulas creates zeros in a line graph | Charts and Charting in Excel | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |