if statementd and ignor ing formulas returning ""

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I sent the empty post by accident!

I am working on a formula to make one of two calculations
depending if the first number (A1) is more or less than
zero. The formula works if the A cell is empty or has a
zero or is >1. BUT the "A" cell is supposed to have a
formula in it that usually returns an empty cell. I get
VALUE. How do I get my formula to work? I can add "0"'s
to the cells but that really clutters up the spreadsheet.

'=IF((A13)>0,ROUND($D13*A13,0),IF(A13<=0,""))

Thanks,


Todd
 
Todd,

I am unable to understand what you mean by:

BUT the "A" cell is supposed to have a
formula in it that usually returns an empty cell. I get
VALUE.

Which cell is "A" cell? A1? A13? something else?
 
Hi Todd!

Try:

=IF(A1="","",IF(A1>0,ROUND($B13*A1,0),""))

The formula returns a "" if A1 is empty, contains a formula that
returns "", or if it is a value less than or equal to zero.

Otherwise it will return $B13*A1 rounded to no decimal places.

Exceptions are:
Error values returned into A1 where the formula returns the same error
value
Where A1 contains the boolean false the formula returns 0
Where A1 contains text the formula returns #VALUE!

There may be others but second coffee needs drinking.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 23rd July: Armenia (Martyr's Day),
Egypt (Revolution Day), Equatorial Guinea (Bata's Fiesta), Fiji
(Constitution Day), Indonesia (National Children's Day), Oman
(National Day), Papua New Guinea (Remembrance Day), Syria (Egyptian
Revolution Day). Observances: Rastafarian (Birthday of Haile
Selassie), Neptunalia (Roman)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
...
...
. . . How do I get my formula to work? I can add "0"'s
to the cells but that really clutters up the spreadsheet.

'=IF((A13)>0,ROUND($D13*A13,0),IF(A13<=0,""))
Click to expand...
...

You could try

=IF(N(A13)>0,ROUND($D13*A13,0),"")
 
Hi Todd!

You'll find that if you post sufficient information and especially the
formula that you have tried, the majority of solutions offered here
will work because most posters do try to test before sending. But we
do slip up :(

Keep having fun!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 24th July: Ecuador (Simon Bolivar's
Birthday), Peru (Fiesta de Santiago), Puerto Rico (Jose Barbosa Day),
Utah (Pioneer Day), Vanuatu (Children's Day), Venezuela (Simon Bolivar
's Birthday)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top