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

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
HIS

"Martin Wheeler" wrote in message
...
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

Hi Nick,
Thanks It works fine
Ta,
Martin
"Nick Hodge" wrote in message
...
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
HIS

"Martin Wheeler" wrote in message
...
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

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.

Martin Wheeler wrote:
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

"Aladin Akyurek" wrote...
=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

?

Harlan Grove wrote:
"Aladin Akyurek" wrote...

=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,"")



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().

and

=N(CW17)-N(CW6)

instead of:

=SUM(CW17-CW16)


How about simply

=CW17-CW16

?


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

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

"Aladin Akyurek" wrote in message
...
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.

Martin Wheeler wrote:
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