How do I make this work?

[Scoober]

Hi,

I cannot make a cell with zero in it add to another cell with a figure and
get an answer?
i.e.

A3 = A1+A2

16 = 0 +16 My formula is 'A1+A2'

I have results in my formula's where A1 or A2 will be populated by a 0, an
empty space, or in fact a number. I then need A3 to be the sum of A1+A2 but I
cannot get my formula to add, instead I get #VALUE?

So the math could even become 0+0=0 <-- but this is less likely.

Can anyone help?

Thanks in advance Scott

 

[Gary''s Student]

1. clear A1 & A2
2. format A1 & A2 to General
3. re-enter the dta in A1 & A2

 

[joeu2004]

I have results in my formula's where A1 or A2 will be populated by a 0,
an empty space, or in fact a number. I then need A3 to be the sum of
A1+A2 but I cannot get my formula to add, instead I get #VALUE?

The problem is likely that A1 or A2 contains text that does not appear
to be number.

But it is difficult to give you concrete and succinct help when you
describe things in the abstract. It would be better if you showed
exactly what is in each cell.

For example, if by "empty space", you really mean a formula that might
result in "" -- e.g. =IF(Z1=Z2,"",123) -- the following might be a
good solution for you:

=sum(A1,A2)

If that does not solve your problem, please post a response that shows
exactly what is in each cell, in the form that you enter it in Excel
(including leading "=" for formulas).


----- original posting -----

 

[Scoober]

Hi I tried =sum(A1,A2) and got the answer 0 even though A1 was 0 and A2 was
4500.

As you have asked I will show below exactly what is in each cell including
the correct cell numbers.

So
Q22: =sum(022,P22)

P22:
=IF(H22=0,"0",IF(H22<300000,"2000",IF(AND(H22>299999,H22<600000),"3000",IF(AND(H22>599999,H22<1000000),"4500",IF(H22>999999,"6000")))))

O22: =L22*N22
(L22 and N22 are cells that have figures
inputted but have no formula's)

As you can see I need to show different values in cell P22 depending on what
value is placed in H22 and then add that value to the answer of O22 and
populate that answer in Q22. The problem I am facing (it seems) is because
one of the answers is 0 it will not add the results in O22 and P22 to Q22?

Thanks Scott

 

[joeu2004]

The problem is: you are putting text strings that look like numbers
in cells instead of numbers. SUM treats all text as the value 0.
Your problem is not the text "0", but the text "4500".

Your IF expression should be written as follows:

=if(H22<=0, 0, if(H22<300000, 2000, if(H2<600000, 3000, if
(H22<1000000, 4500, 6000))))

Caveat: I am taking some liberties with my interpretation of your
requirements. If I missed the mark and it is not clear how to morph
the above to meet your needs, post back.

Arguable better (more flexible, anyway) would be:

=if(H22<=0,0,lookup(H22,
{0,2000;300000,3000;600000,4500;1000000,6000}))

Note: The LOOKUP formula could be simplified, depending on
interpretation of your requirements.

PS: You can now write =O22+P22 instead of =SUM(O22,P22).


----- original posting -----

 

[Scoober]

Thank you this has worked a treat.

Would you know how to tidy the cells below O22,P22, and Q22 which use the
same formula but some times have no data in them?

i.e.
How can I make O22 remain empty instead of showing a 0 when there is no
entries in either L22 or N22, and will this effect your formula's in P22 and
Q22 that I have just entered? ( I used the 'Lookup' formula)

I'm sorry to be a bore, but I'm almost there and have pulled out almost all
of what little hair I had left.

Thanks once again scott.

 

[joeu2004]

How can I make O22 remain empty instead of showing a 0
when there is no entries in either L22 or N22

O22: =if(or(L22="",N22=""),"",L22*N22)

will this effect your formula's in P22 and Q22 that I
have just entered? ( I used the 'Lookup' formula)

Q22: =sum(O22,P22)

The key is: O22+P22 does not tolerate text like ""; but if the cell
is completely empty (i.e. no formula or constant), O22+P22 would
work. On the other hand, SUM(O22,P22) is more tolerant, ignoring text
as well as empty cells.


You have not mentioned anything that would affect the P22 formula.
But suppose you want P22 to be zero when H22 appears blank, or you
want P22 to appear blank in that case. Then the appropriate formula
below:

=if(or(H22="",H22<=0),0,
lookup(H22,{0,2000;300000,3000;600000,4500;1000000,6000}))

=if(H22="","",if(H22<=0,0,
lookup(H22,{0,2000;300000,3000;600000,4500;1000000,6000})))

HTH


----- original posting -----

 

[Scoober]

Hi Joe,

you have done it again, thank you.

I tried the following to make Q22 also blank if O22 and P22 where blank but
i got a 'FALSE' message.

=if(or(O22="",P22=""),""=SUM(O22,P22))

Can you tell me where I went wrong?

Scott.

 

[joeu2004]

i got a 'FALSE' message.
=if(or(O22="",P22=""),""=SUM(O22,P22))
Can you tell me where I went wrong?

Assuming that you cut-and-pasted the formula exactly as you entered
it, the problems are: you are missing a comma, and you have an
extraneous "=". You should have written:

=if(or(O22="",P22=""),"",SUM(O22,P22))

However, if you are going to the trouble of testing O22 and P22, you
should no longer need SUM. So you could write:

=if(or(O22="",P22=""),"",O22+P22)

 

[Scoober]

Fantastic thank you. This of course has flowed onto the last two formula's I
have on my spreadsheet.

Can I explain.

By using your last formula =if(or(O22="",P22=""),"",O22+P22) Q22 is now
blank,which looks great,however this has effected U22 and V22.

U22 has the formula T22-Q22 so it now shows 'value!' and V22 has a formula
of =U22/12 which now shows '#####'

I have tried this and it seems to have worked.

U22: =IF(OR(T22="",Q22=""),"",T22-Q22)
V22: =IF(OR(U22=""),"",U22/12)

Do you agree?

 

[joeu2004]

U22 has the formula T22-Q22 so it now shows 'value!'

Yes, this is an unfortunate consequence of conditionally putting a
null string ("") into a cell. IMHO, Excel should treat it the same as
an empty cell (WYSIYG), especially since some Excel functions do just
that (Principle of Least Surprise, aka consistency). But we cannot
reinvent Excel here. (And there are compatibility issues to
consider. But they are not insurmountable.)

and V22 has a formula of =U22/12 which now shows '#####'

Ostensibly, that simply means your cell is not wide enough to display
the result. But sometimes that means the result is not what you
expected or intended (e.g. an infinitesimally small number that is
represented with scientific notation). And in this case, I presume it
means that the cell is not wide enough for #VALUE! error.

I have tried this and it seems to have worked.
U22: =IF(OR(T22="",Q22=""),"",T22-Q22)
V22: =IF(OR(U22=""),"",U22/12)
Do you agree?

Yes -- except V22 should be simply =IF(U22="","",U22/12).

Another approach would be to use the N() function prolifically, and to
use either Conditional Format or a Custom format to hide zeros. For
example:

U22: =N(T22)-N(Q22)
V22: =N(U22)/12

formatted with the Custom format "#;-#;" (without quotes).

 

[Scoober]

Hi Joe,

I've run into a problem with an earlier formula we wrote.

Q22: =IF(OR(O22="",P22=""),"",O22+P22)

This formula will not populate Q22 with the figure in P22 if there is no
value in O22. (which sometimes there is within the workings of the
spreadsheet)

How do I get around this problem?

Cheers Scott

 

[joeu2004]

Q22:  =IF(OR(O22="",P22=""),"",O22+P22)

This formula will not populate Q22 with the figure in
P22 if there is no value in O22.

=if(and(O22="",P22=""),"",sum(O22,P22))

 

[Scoober]

Thanks Joe,

I cannot thank you enough. The spreadsheet is looking fantastic. (mainly
because of your help)

Can I trouble you for one more formula.

I am trying to add a formula that will subtract a tax fee if a 'y' is
printed in the tax column, at the same time leave the cell empty if there is
no figure entered in the initial cell.

i.e.

K27: If H27 is empty then K27 is to remain empty, however if h27 is
populated then k27 has the same figure, however if 'Y' is entered into G27
then H27 is to be divided by 1.125


This is what I have come up with that is not working:

=IF(OR(H27=0,""),G27=Y,H27/1.125) <-- I get #VALUE! in K27


Once again your help would be appreciated.

Cheers Scott

 

[joeu2004]

K27: If H27 is empty then K27 is to remain empty,
however if h27 is populated then k27 has the same
figure, however if 'Y' is entered into G27
then H27 is to be divided by 1.125

=if(H27="","",if(G27="Y",H27/1.125,H27))

 

[Scoober]

Thank you for all of your helkp

=if(H27="","",if(G27="Y",H27/1.125,H27))