Help with IF Formula

**Jarristopheles** · 10-23-2009, 10:53 AM

Hey everyone,

I was wondering if I could get some assistance with an IF formula I am working on:

=IF('Agent Statistics'!K27> 83, "Pass", IF('Agent Statistics'!K27< 83, "Fail", IF('Agent Statistics'!K27<> 0, "None")))

Basically, If the score is above an 83.00, I need it to say Pass, and that works, and if it is under an 83.00, i need to say Fail, and that works. Here is the problem I am running in to. The tab I am pulling the data from pulls data from a page that contains statistics of the particular agent. That agent may not have a score a certain week, so it will show 0.00. I need my formula basically say:

If K27 is 0.00, then display None.

Can anyone help me with this? I just can't seem to figure it out.

Thanks

**NBVC** · 10-23-2009, 10:59 AM

Try

=IF('Agent Statistics'!K27= 0, "None",IF('Agent Statistics'!K27> 83, "Pass", "Fail"))

also, what if it equals 83? with my formula it will put Fail in the cell... if you don't want that, change >83 to >=83

**Jarristopheles** · 10-23-2009, 11:16 AM

Originally Posted by NBVC

Try

=IF('Agent Statistics'!K27= 0, "None",IF('Agent Statistics'!K27> 83, "Pass", "Fail"))

also, what if it equals 83? with my formula it will put Fail in the cell... if you don't want that, change >83 to >=83

I tried the formula, and the Agent in cell K27 on the Agent Statistics tab had a 47.00 for the week, and it is displaying "None" as if it were 0, which it should be displaying Fail according to the formula, so it seems to not be working unless I am completely missing something. The formula looks good, but I just can't figure it out.

I realized if it equaled 83, it will have some issues, but I cannot have it where it =83, because if the score if above an 83, then I need to say pass as well, unless having it =83 will not be an issue if the score is above 83.

**PBisson** · 10-23-2009, 11:00 AM

Could this work
=if('Agent Statistics'!K27= 0, "None",If('Agent Statistics'!K27<83, "Fail","Pass"))

Beat me to the post. "shake my fist"

**NBVC** · 10-23-2009, 11:17 AM

Try:

=IF('Agent Statistics'!K27+0= 0, "None",IF('Agent Statistics'!K27+0> 83, "Pass", "Fail"))

You probably have the 47 entered as a text field...

**Jarristopheles** · 10-23-2009, 01:38 PM

Thank you for your help! This works perfectly!

The 47 comes from an average formula pulled from a data tab I have, but in essence, it still is a text field.

Much thanks again.

**jimmybrine** · 10-26-2009, 08:41 AM

Gday Ive just started using excel and this is my first time on a forum so i hope im on the right track. In column D i input either S(short) or L(long) when i input S i want column K to subtract column H from column G. And when I input L i want column K to subtract column G from column H. If anyone could help me with this it would be much appreciated.

**NBVC** · 10-26-2009, 08:47 AM

jimmybrine,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.