Need formula help! Removing a neg# & #DIV/0!

[Guest]

D28 is entered daily & cell has no formula
E28 is =D28-D27 (copied down page)
F28 is =E28/D28 (copied down page)

Day total Day increase Percentage
----------+---------------+-------------
(D28) (E28) (F28)
1215.02 6.74 0.005547234
----------+---------------+-------------
(D29) (E29) (F29)
1231 15.98 0.012981316
----------+---------------+-------------
D29 is entered daily & cell has no formula
E29 is D29-D28 (copied down page)
F29 is E29/D29 (copied down page)



But in the next line & lines before I enter a # in the D colum cell I get
this...
I need to keep the formula and remove the data shown and from being totaled
in E columb... can this be done?

(D30) (E30) (F30)
-1231 #DIV/0!

D30 is to entered the next day but I need to total the E columb without the
Negitive # in the cell...

 

[Guest]

You can structure the formulas to "behave" until valid data has been entered:

Instead of:
=D30-D29
in E30, use:
=IF(D30="","",D30-D29)

Instead of
=E30/D30
in F30, use:
=if(D30="","",E30/D30)

continue this downwards.

 

[Guest]

Hey;

I have seen a lot of posts with this request. John P. and the others suggest
using this instead.

No: =IF(D30="","",D30-D29)

Yes: =IF(D30="",n/a(),D30-D29)

I think that's right. I never use it. Your way will work, if there are not
other calculations, Gary. But if there are calculations down the line then
this will add a zero (0) to the calculation. 0*a=0!

God Bless

Frank Pytel

 

[Guest]

Hello Again

Wow. I just tried that (na() not n/a(). Probably why I can never get it to
work.) That leaves a nasty old bogger on your spreadsheet. I'll stick with
the "" statement until I can figure something else out.

Frank

 

[David Biddulph]

If you are worried about the N/A values showing on the spreadsheet, you can
use conditional formatting to turn the font colour white (or the same as
your background colour) if the cell contains a NA().