IF/ISERROR Formula issues

Ok the if(isna gets me the M I am looking for, is there a way to incorporate this into the other formula so that a single cell can output one of the 3 results I am looking for? (P,F,M)

That's an if inside another if.

I'll give it in 'pseudocode' and leave it to you to translate it to the Excel-syntax, with the correct positioning of comma's and parentheses.

if M-condition
then "M"
else
if (P-condition)
then "P"
else "F"

or whatever sequence of the conditions is appropiate for you.

This is beginning to look like real programming.

Kees

I would just add one thing.

When doing nested IF function formulas, (as with any nested formula), remember to count the open and closed brackets as you go along. Time and time again I have failed to get nested formulas to work simply because I forgot to close a bracket. There should be as many closed brackets as there are open; eg;

=IF(ISNA(B5),IF(A5=1,"M",IF(A5=2,"P",IF(A5=3,"F"))))
will work, but

=IF(ISNA(B5),IF(A5=1,"M",IF(A5=2,"P",IF(A5=3,"F")))
will not. Excel will not accept the formula because there is one to few close brackets.

Mark

Sorry guys but I still can't get it to go.
Here are the 3 options I need to denote:
85.00% P
#N/A M
100.00% F

I tried using a similar formula to the last post. and when I did, it returns a FALSE for any cell with a score, and #N/A when it sees #N/A.

Again I can get 2 options to note on the standard IF formula using this: =IF(F26>=90%,"P","F"). Cell F26 is a vlookup formula to give me the score, and will show #N/A when a score has not registered and I want this if formula to show M.

Thanks to all that are helping me with this.

At the risk of complicating the issue... you could try IsError()

This covers any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

The formula could be as follows:

=IF(ISERROR(VLOOKUP(EmployeeName,DataRange,2,FALSE)),"M",IF(VLOOKUP(EmployeeName,DataRange,2,FALSE)>=0.9,"P","F"))

Translation:
If Vlookup error then "M"
Else If Vlookup value >= 90% then "P"
Else "F"

Since you already have the VLookup working ok you should be able to just past it over the my sample one.

Nice one.

One thing about the VLOOKUP function though. In some circumstances the range being looked up has to be sorted in ascending order. This means that if you have a column of figures in which position is important because they relate to other specific cells, then sorting them may disrupt that relation. I must admit I don't use VLOOKUP that much myself because of this, so my knowledge of using it isn't too great.

In the case of this problem, if the cell that contains these scores have a relation to a particular employee in a list, then is this going to cause a problem?

If this suggestion does not work, can you tell us what range the VLOOKUP in cell F26 is looking at? Also, where does this information come from? What I mean is, is there another place on the spreadsheet where a score is registered/input, but if no score is registered somehow an #N/A error is generated?

What if, where no score is registered/input., the cell defaults to 0, (zero), and not #N/A, (the cell already has a zero, and is only changed when a score is registered)?

Mark

OK, i think the following gives you what you want.

Let's say that you're doing the lookup from Sheet1 with data on Sheet2. Sheet2 data range is B:F with name in B and score in F.

VLOOKUP formula in F2 is:
=VLOOKUP(B2,Sheet2!B:F,5,FALSE)
.......[note: F is 5th column in range]
.......where B2 contains employee name

ISERROR formula in G2 is:
=IF(ISERROR(F2),"",F2)
To treat score=0 and "M" use this =IF(OR(ISERROR(F2),F2=0),"",F2)

P/F/M formula in H2 is:
=IF(G2="","M",IF(G2=<0.9,"F","P"))

Combined into one is:
=IF(IF(ISERROR(VLOOKUP(B2,Sheet2!B:F,5,FALSE)),"",VLOOKUP(B2,Sheet2!B:F,5,FALSE))&=&"","M",IF(IF(ISERROR(VLOOKUP(B2,Sheet2!B:F,5,FALSE)),"",VLOOKUP(B2,Sheet2!B:F,5,FALSE))&=&< 0.9,"F","P"))
......take out the '&' as these were added avoid HTML errors in this post