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(L120,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

=IF(L120,L12-G12,"") into column M,
but this returns #N/A if no test result is
entered into column L

For that formula to return #N/A either L12 and/or G12 must also contain
#N/A.

Try it like this:

=IF(COUNT(G12,L12)=2,IF(L120,L12-G12,""),"")

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
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(L120,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

Is column L derived through a formula? I only ask because against a blank
cell, your formula should work just fine. In fact, the more common error to
see with the IF formula is the #VALUE! error, not #N/A, so I'm curious as to
what else is going on in your spreadsheet.

If problem persists, you could use an error checker. Not very fancy, but
effective:
=IF(ISNA(IF(L120,L12-G12,"")),"",IF(L120,L12-G12,""))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Mrs T." wrote:

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(L120,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

Your formula won't return #N/A. Is there an #N/A anywhere else in the sheet?
Alan.
"Mrs T." wrote in message
...
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(L120,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

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

"Luke M" wrote:

Is column L derived through a formula? I only ask because against a blank
cell, your formula should work just fine. In fact, the more common error to
see with the IF formula is the #VALUE! error, not #N/A, so I'm curious as to
what else is going on in your spreadsheet.

If problem persists, you could use an error checker. Not very fancy, but
effective:
=IF(ISNA(IF(L120,L12-G12,"")),"",IF(L120,L12-G12,""))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Mrs T." wrote:

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(L120,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

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 :)

"Alan" wrote:

Your formula won't return #N/A. Is there an #N/A anywhere else in the sheet?
Alan.
"Mrs T." wrote in message
...
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(L120,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

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." wrote in message
...
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 :)

"Alan" wrote:

Your formula won't return #N/A. Is there an #N/A anywhere else in the
sheet?
Alan.
"Mrs T." wrote in message
...
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(L120,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

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

"Ronald R. Dodge, Jr." wrote:

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." wrote in message
...
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 :)

"Alan" wrote:

Your formula won't return #N/A. Is there an #N/A anywhere else in the
sheet?
Alan.
"Mrs T." wrote in message
...
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(L120,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