Average & blank cells

[Guest]

Hi guy's,
I have an issue with a sheet and an average formula:

I am subtracting one date from another to achieve a result (no problems) i have used the following if statement:
=IF(E5-D5=0,"",E5-D5) to not show zero values as there is a long list of dates and not all the list would be fillied in all the time.
My problem is that i need to create an average for these figures but need to also include the zero's in the average formula that may have the same start and end date (which results in zero but not displayed due to my if statement) without including the zeros in the cells where no date has been added yet.
If i take the if statement out i obviously get a long list of zeros that affect the resulting average formula, i'd like to not display zeros where the calculation has no dates entered but display zeros where dates have been entered but with a result of zero ie 01.01.04 - 01.01.04 = 0
Is there any solution to this?
Have i confused everyone enough.

Thanks in advance.
Leo

 

[Nick Hodge]

Leo

If I understand you correctly

The following will give you blank if either cells have no data or zero if
the subtracting gives you zero

=IF(OR(E5="",D5=""),"",IF(E5-D5=0,0,E5-D5))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Biff]

Hi Leo!

Why not just rewrite your formula to test for blanks:

=IF(OR(E5="",D5=""),"",E5-D5)

This way only the zeros you need displayed will be.

Biff

-----Original Message-----
Hi guy's,
I have an issue with a sheet and an average formula:

I am subtracting one date from another to achieve a

result (no problems) i have used the following if
statement:

=IF(E5-D5=0,"",E5-D5) to not show zero values as there is

a long list of dates and not all the list would be fillied
in all the time.

My problem is that i need to create an average for these

figures but need to also include the zero's in the average
formula that may have the same start and end date (which
results in zero but not displayed due to my if statement)
without including the zeros in the cells where no date has
been added yet.

If i take the if statement out i obviously get a long

list of zeros that affect the resulting average formula,
i'd like to not display zeros where the calculation has no
dates entered but display zeros where dates have been
entered but with a result of zero ie 01.01.04 - 01.01.04 =
0

 

[Guest]

thanks very much Nick, both formulas did the job.
sometimes the simple ones are the best.
Great help, keep up the good work.
Leo

 

[Guest]

Thaks Biff,
this was a great help, keep up the good work.
CHeers
Leo