Amendment to formula required to avoid having #N/A displayed

[robertguy]

Hi,

Can any advise me how I can amend the two formals in cells O32 and S32 (see below) to avoid getting #N/A in the cells if there is no valid data in any of the other cells in the formulas

Formula in cell O32

=VLOOKUP(($J$12:$J$5000 & " Total"),$A$12:$G$5000,7,FALSE)+'WAF Cover'!O22

Formula in cell S32


=IF(N32-(N32/100*80)<R32,"",IF(N32="",""," Warning 20% or less Remains!"))


Any assistance would be greatly appreciated


Many thanks in advance


Rob


N.B. Excel version 2003

[somesoldiers]

use the IFERROR function

eg =iferror((value),value_if_error)

=iferror(IF(N32-(N32/100*80)<R32,"",IF(N32="",""," Warning 20% or less Remains!")))inset what you want here between " " or leave a space for a blank")

sorry not in excel at the mo to test

[somesoldiers]

actually just noticed this will not work in 2003

For 2003
=if(iserror(IF(N32-(N32/100*80)<R32,"",IF(N32="",""," Warning 20% or less Remains!"))),"what you want instead of the NA",(IF(N32-(N32/100*80)<R32,"",IF(N32="",""," Warning 20% or less Remains!")))

should do it or post a sample of your workbook and I'll do it for you

[robertguy]

somesoldiers

many thanks for the code it worked as required


Thanks again


Rob