#VALUE!, #N/A

[Martin Wheeler]

xl2003
I am getting the above in cells that are either blank or refering to a cell
that has alpahbetical data in it rather than numbers
My spreadsheet contains a table a data and then tables of formulas that work
the data. It is the data of formulas that are the problem
What I want is to have blank cells

I am using =IF(ISERROR(SUM((G20-F20)/F20)),"",SUM((G20-F20)/F20))
successfully in the empty cells that are part of the data table. But get
#N/A in the following =VLOOKUP(CD18, $Q$7:$R$26, 2,FALSE) when the
corresponding data cell has "scr" in it instead of a number. I need to leave
"Scr" in


I am also getting #Value! when using =SUM(CW17-CW16).

I have tried using =If(Iserror..... but am not getting anywhere.

Any help would be greatly apprciaed.
Ta,
Martin

 

[Nick Hodge]

Martin

=IF(ISNA(VLOOKUP(CD18, $Q$7:$R$26, 2,FALSE)),"scr",VLOOKUP(CD18, $Q$7:$R$26,
2,FALSE))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Martin Wheeler]

Hi Nick,
Thanks It works fine
Ta,
Martin

Martin

=IF(ISNA(VLOOKUP(CD18, $Q$7:$R$26, 2,FALSE)),"scr",VLOOKUP(CD18,
$Q$7:$R$26, 2,FALSE))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Aladin Akyurek]

Try:

=IF(N(F20),(N(G20)-F20)/F20,"")

instead of:

=IF(ISERROR(SUM((G20-F20)/F20)),"",SUM((G20-F20)/F20))

and

=N(CW17)-N(CW6)

instead of:

=SUM(CW17-CW16)

assuming that the relevant cells might possibly house a formula blank
(i.e., "").

Regarding the lookup formula, if the lookup value in CD18 is text like
"scr" while the match-range $Q$7:$Q$26 of $Q$7:$R$26 consists of
numbers, an #N/A would be returned. While not an ideal solution:

=IF(ISNUMBER(MATCH(CD18, $Q$7:$Q$26, 0)), VLOOKUP(CD18, $Q$7:$R$26,
2,0),ReturnValue)

where ReturnValue can be anything like 0, "", or CD18, the lookup value
itself.

If $Q$7:$R$26 is set in ascending order on its match-range, you can
have, performancewise, a much better lookup formula.

 

[Harlan Grove]

=IF(N(F20),(N(G20)-F20)/F20,"")

instead of:

=IF(ISERROR(SUM((G20-F20)/F20)),"",SUM((G20-F20)/F20))

Quibble: N("99") == 0, but "99" is an acceptable denominator. Maybe

=IF(COUNT(1/F20),G20/F20-1,"")

and

=N(CW17)-N(CW6)

instead of:

=SUM(CW17-CW16)

How about simply

=CW17-CW16

?

 

[Aladin Akyurek]

Quibble: N("99") == 0, but "99" is an acceptable denominator. Maybe

=IF(COUNT(1/F20),G20/F20-1,"")

I'm inclined to consider text-formatted numbers, if unintended, as input
errors. They should be tracked down with formulas that expressly audits
the outcomes. No objection though against substituting COUNT() for N().

How about simply

=CW17-CW16

?

If CW17 is a formula-blank (as stipulated in my qualification), we would
still have #VALUE!.

 

[Martin Wheeler]

Hi Aladin,
Thanks for the help.
I am now using =N(CW17)-N(CW6) and =IF(N(F20),(N(G20)-F20)/F20,"") . They
are doing the job just fine.
Thanks
Martin