Formulas go away

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a fillable form in Excel and had my formulas all setup, but
with the formulas I was using it was putting numbers in the cells where I
wanted the cell to either be a blank cell or a zero, so when I changed the
cells to zero's one by one the formulas in those cells were gone. Why does
this happen, and what do I need to do to so the formulas will stay where they
should be in the spredsheet?
Thanks,
PE
 
There ae possibly two separate topics at issue:

1. If you put a formula in a cell and later a value in the same cell, the
formla will disappear.

2. If A1=1, the formula =IF(A1=1,"",2) will appear to vanish in the cell,
but will still show up in the formula bar.
 
It looks like you are overwriting the formula with the value 0. Depending on
your case, you may take a look at the IF function, as it will allow you to
display a value or a blank without having to overwrite the formula.
I may be doing the wrong assumption, if so please post some example of what
you are trying to achieve.

Hope this helps,
Miguel.
 
Miguel,
I will try an example and hopefully not confuse you in doing so.
With the formula in C5 down being =IF(D4=0,(C4+1),(D4+1))

C D
4 6 0
5 7 8
6 9 11
7 12 0
8 13 15
9 16 25
10 0 0
11 0 0

Ok, where the zeros are I want them to stay zero's until and entry is put in
that cell, but with the formula I am using it will continue to add one all
the way down column C. So as I explained in my original message I would go
through after having my formulas in each cell and type the zero back in, but
then my formula is gone. I have really been struggling with getting the
formula's correct to do what I want the spredsheet to do.

Thanks,
PE
 
Ok, the issue I see here for using a single formula is the criteria for
getting a 0 on column C. If you can write the criteria, you can code it on a
nested IF formula, for example, this check first if the column D is 0, if so,
if the column C is 0 or 16, and if not, if the column D is 25:
=IF(D4=0,IF(OR(C4=16,C4=0),0,(C4+1)),IF(OR(D4=25),0,(D4+1)))
This can get quite complex, you can use something a little more simpler like:
=IF(D4=0,IF(C4=0,0,(C4+1)),IF(C4=0,0,(D4+1)))
Than will keep the 0 if both preceding values are 0, and manually enter the
0 in the positions needed.

Hope this helps,
Miguel.
 
Back
Top