NEED CELL SHOW AS BLANK, NOT #VALUE!

[mikeburg]

WHEN I ENTER THE FOLLOWING IN CELL C1,
=IF(AND(A1>0,A1-B1,"")

THEN IN CELL C10 I ENTER
=C1*5.10, I GET #VALUE! RATHER THAN ZERO.

THE REASON I AM USING "" IN THE FORMULA IS I WANT C1 TO SHOW AS BLANK NOT WITH 0 (ZERO) AS 0 WOULD DO. BUT THE "" IN THE FORMULA CAUSES THE #VALUE!.

HOW CAN I GET C10 TO SHOW AS ZERO OR BLANK AND CELL 1 SHOW BLANK.

MIKEBURG

 

[bluedragon2]

=IF(C1="",0,C1*5.1)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

What is the formula in cell C1? The formula you listed doesn't make any sense. Excel wouldn't allow you to enter that formula because it has the wrong number of arguments and is missing parentheses.

Assuming you have a functional formula in C1 that is returning "", then use this formula in C10:
[COLOR=blue white]=IF(C1="",0,C1*5.1)[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[byundt]

How about these formulas:
=IF(AND(A1>0),A1-B1,"") for cell C1
=N(C1)*5.10 for cell C10

N(C1) returns a number if C1 contains a number. It returns 0 if C1 contains text.

As pointed out by anotherhiggins, your formula for cell C1 was missing something. I added a parenthesis to close your AND, but you could add other criteria inside the AND function if you like.

Brad

 

[kpetursson]

You could change the formatting for cell C1
Right Click on the cell.
Click Format Cells...
Select the tab Number
Under Catigory select Custom.

use the following
#,##0.00;#,##0.00;

To Create a custom format use the following guide showing the different formats with ; seperating them...
format for...
positive#;negative#;zero#;Text

My above example will make the cell blank for Zeros, and will give 2 decimal places for all other numbers, and it will not affect text.

If you have a number typed into the cell, the Format cells box will give you a preview of the result.

This makes for a muck cleaner (formula wise) spreadsheet, and helps to do away with a lot if extra if statements.
If can be frustrating at first to learn all the trick to formatting codes, but in the end your formulas will thank you.

Kevin Petursson

 

[ETID]

Why do you need the "AND"

=IF(AND(A1>0,A1-B1,"")

or did you intend to express

If("condition","Then","Else")

I.E.
=IF(A1>0,A1-B1,"")

 

[ETID]

...OR THIS

=IF(iserror(A1-B1),"",A1-B1)