Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help! Removing a neg# & #DIV/0!
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help! Removing a neg# & #DIV/0!
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. -- Gary''s Student - gsnu200741 "j5b9721" wrote: 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help! Removing a neg# & #DIV/0!
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200741 "j5b9721" wrote: 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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help! Removing a neg# & #DIV/0!
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200741 "j5b9721" wrote: 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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help! Removing a neg# & #DIV/0!
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(). -- David Biddulph "Frank Pytel" wrote in message ... 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200741 "j5b9721" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear Cell Without Removing Formula | Excel Worksheet Functions | |||
keep number when removing formula | Excel Discussion (Misc queries) | |||
Removing formulas but keeping formula totals. | Excel Discussion (Misc queries) | |||
What Excel Formula = finding dup records and removing them? | Excel Worksheet Functions | |||
Cannot enter formula in a cell after removing a circular formula | Excel Worksheet Functions |