How do I get rid of FALSE, #VALUE!, ###, in cells?
 

Hello,

I have in some cells some formulas that look for a value in other cells
and then do their calculations.
But when these cells, that are looked at, don't have a value in them
the other cells, waiting to calculate, end up with either a FALSE, ###,
or #VALUE! in the cell.

I would like to have these be blank when nothing is to be calculated.

Am I missing something in the formula to make these cells blank?

Thanks a lot,

Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=527813

How do I get rid of FALSE, #VALUE!, ###, in cells?
 

You could start by posting the formula(s)

=IF(looked_at_cell="","",your_formula)

would take care of empty cell

If you get false you have either an if function without anything in the (if
false do what)
or a condition like =A22


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"AnimatorElf"
wrote in message
...

Hello,

I have in some cells some formulas that look for a value in other cells
and then do their calculations.
But when these cells, that are looked at, don't have a value in them
the other cells, waiting to calculate, end up with either a FALSE, ###,
or #VALUE! in the cell.

I would like to have these be blank when nothing is to be calculated.

Am I missing something in the formula to make these cells blank?

Thanks a lot,

Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile:
http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=527813

How do I get rid of FALSE, #VALUE!, ###, in cells?
 

Sorry, here they are.

=SUM(H15+J15) This one gives ###-- Cell H15 has =E9 in it.

=SUM(U8+X8+AA8+AD8+AG8) This one gives #VALUE!-- Cell X8 has =E10 in
it.

=IF(Z3="Fine","8",IF(Z3="Gargantuan","-4",IF(Z3="Huge","-2",IF(Z3="Large","-1",IF(Z3="Medium","0",IF(Z3="Small","1",IF(Z3="Tin y","2",IF(Z3="Diminutive","4"))))))))
This one gives FALSE-- Cell Z3 requires one of those 8 words to be
placed in it for this formula to "work".

Almost all of my formulas require a number to be placed in Cells C8
thru C13. Once a number is placed in one of those cells, they all spit
out what I need, no errors.

Thanks again.
Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=527813

How do I get rid of FALSE, #VALUE!, ###, in cells?
 

1. If you have numbers in H15 and J15 this ##### means that you should widen
the column

also if you use SUM there is no need to use operators like +

=SUM(H15,J15)

2. The values error is because there is a text value in one of the cells, if
you replace the + signs with commas the text will be ignored which otoh
might not be what you want. Make sure all values are numbers


3. You might want to remove the quotations around the numbers in your
formula "8" means that the result is text and will throw a value error, that
is not the reason you get false though. What do you want to happen if none
of the conditions are TRUE?

=IF(Z3="Fine",8,IF(Z3="Gargantuan",-4,IF(Z3="Huge",-2,IF(Z3="Large",-1,IF(Z3="Medium",0,IF(Z3="Small",1,IF(Z3="Tiny",2, IF(Z3="Diminutive",4,"Something
else"))))))))

will return "something else"

=IF(Z3="Fine",8,IF(Z3="Gargantuan",-4,IF(Z3="Huge",-2,IF(Z3="Large",-1,IF(Z3="Medium",0,IF(Z3="Small",1,IF(Z3="Tiny",2, IF(Z3="Diminutive",4,""))))))))


will return a blank, I would probably change the formula to

=IF(Z3="","",VLOOKUP(Z3,C7:D14,2,0))

where C7"C14 would hold all possible words and D7:D14 the numbers
(replace the range with a range that you want to use)

or hardcoded

=IF(Z3="","",VLOOKUP(Z3,{"Fine",8;"Gargantuan",-4;"Huge",-2;"Large",-1;"Medium",0;"Small",1;"Tiny",2;"Diminutive",4},2, 0))
--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"AnimatorElf"
wrote in message
...

Sorry, here they are.

=SUM(H15+J15) This one gives ###-- Cell H15 has =E9 in it.

=SUM(U8+X8+AA8+AD8+AG8) This one gives #VALUE!-- Cell X8 has =E10 in
it.

=IF(Z3="Fine","8",IF(Z3="Gargantuan","-4",IF(Z3="Huge","-2",IF(Z3="Large","-1",IF(Z3="Medium","0",IF(Z3="Small","1",IF(Z3="Tin y","2",IF(Z3="Diminutive","4"))))))))
This one gives FALSE-- Cell Z3 requires one of those 8 words to be
placed in it for this formula to "work".

Almost all of my formulas require a number to be placed in Cells C8
thru C13. Once a number is placed in one of those cells, they all spit
out what I need, no errors.

Thanks again.
Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile:
http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=527813