fix #N/A error

[Mrs T.]

In a list of assessment data I have current test scores in column L and the
previous test score in column G. I have a formula that works out the
difference between the 2 scores... =IF(L12>0,L12-G12,"") into column M, but
this returns #N/A if no test result is entered into column L which is causing
problems when I'm trying to record a macro to automatically sort data. I have
tried to change the formula to lose the #N/A but am having to admit defeat!
Data looks like this

G L M
14 15 1
14 13 -1
14 14 0

Any help greatly appreciated!!
Mrs T

 

[Mrs T.]

Thank you both - the IF(COUNT worked perfectly. The IF(ISNA returned a
#VALUE! error, maybe because the data in column L comes via
=IF(K12="","",LOOKUP(K12,$AK$12:$AK$55,$AL$12:$AL$55)) or more likely
because I've messed it up in the translation!!
Thanks again
Mrs T

 

[Mrs T.]

P.S. you're all absolutely right...it was an #N/A error in column M but I
managed to fix the #N/A error in column L (more good luck than good
management!) and the error in M had magically transformed itself into a
#VALUE! error without my notice. I'd bet my life a man designed Excel - give
me plain and purl knitting any day!!!
Mrs T

 

[Ronald R. Dodge, Jr.]

Glad to hear your issues has been fixed. I also have in the past ran into
both errors, #NA! and #VALUE! depending on the set of circumstances. In
most cases dealing with lookups, I use the functions of IF, ISERROR, MATCH,
INDIRECT, and ADDRESS like the following

=IF(ISERROR(MATCH(C134,INDIRECT(T1&"!A:A"),0)),"",INDIRECT(ADDRESS(MATCH(C134,INDIRECT(T1&"!A:A"),0),2,,,T1)))

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Mrs T.]

Thanks for the advice Ronald, all help is greatly appreciated and I'll
definitely give it a go.
Mrs T