Solved If function to show whether it is in A,B,C or C+ sta

Microsoft Excel 2010 - complete product...
July 10, 2012 at 02:50:09
Specs: Windows 7, 8
Field A1 Enter a reading (1077). Field A2 Set reading. Field A3. A1 - A2 = 4 Now this 4 will be in a specific standard.
-3 to 3 = A Standard
-3.1 to -7 = B Standard
3 to 10 = B Standard
-7.1 to -12 = C Standard
10.1 to 18 = C Standard
<-12.1 =C+ standard
>18.1 = C+ Standard.
I use the following IF function in Access and it work well but can't get it worked in Excel.
=IIf(([T01] Between -3 And 5),"A",IIf(([T01] Between 5.1 And 12),"B",IIf(([T01] Between -3.1 And -7),"B",IIf(([T01] Between 12.1 And 25),"C",IIf(([T01] Between -7.1 And -10),"C","C+")))))
Thanks

See More: If function to show whether it is in A,B,C or C+ sta

Report •

#1
July 10, 2012 at 06:48:21
✔ Best Answer
Try this,
It's not real elegant, but seems to work:

=IF(OR(A9<-12,A9>18),"C+",IF(OR(AND(A9>=-12,A9<-7),AND(A9>=10,A9<18)),"C",IF(OR(AND(A9>=-7,A9<-3),AND(A9>3,A9<10)),"B","A")))

MIKE

http://www.skeptic.com/


Report •

#2
July 10, 2012 at 07:47:36
-3 to 3 = A Standard
3 to 10 = B Standard

Do you see a problem here? ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
July 10, 2012 at 12:41:28
In addition, many of the values used in your Access function don't match the values in the table you posted.

What's up with that?

Example 1:

-3 to 3 = A Standard

IIf(([T01] Between -3 And 5),"A",

Example 2:

-7.1 to -12 = C Standard
10.1 to 18 = C Standard

IIf(([T01] Between 12.1 And 25),"C",IIf(([T01] Between -7.1 And -10),"C"

I don't see how that function could have worked in Access based on your posted requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 10, 2012 at 22:04:06
What I did was copy the function in Access for Rail track Gauge Standards. The standards that I used are for Rail track cant standards. It doesn’t matter whether it is -3 to 5 or -3 to 3, the bottom line is I need it to work. Any number can be used there as long as it gives me the correct answer. And yes I did make a typing error. I corrected it

If True:
-3 to 5 = "A" Standard
5.1 to 12 = "B"
-3.1 to -7 = "B"
12.1 to 25 = "C"
-7.1 to -10 = "C"

If False:
<-10.1 =C+ standard
>25.1 = C+ Standard.

IF function in Access and it work well.
=IIf(([T01] Between -3 And 5),"A",IIf(([T01] Between 5.1 And 12),"B",IIf(([T01] Between -3.1 And -7),"B",IIf(([T01] Between 12.1 And 25),"C",IIf(([T01] Between -7.1 And -10),"C","C+")))))


Report •

#5
July 10, 2012 at 23:52:11
Well, it seems like you either made more than one typing error, or you've changed the requirements.

Here are your 2 sets of requirements, side by side. They are quite different.

-3 to 5 = "A" Standard      -3 to 3 = A Standard
 5.1 to 12 = "B"             3 to 10 = B Standard
-3.1 to -7 = "B"            -3.1 to -7 = B Standard
 12.1 to 25 = "C"            10.1 to 18 = C Standard
-7.1 to -10 = "C"           -7.1 to -12 = C Standard
<-10.1 =C+ standard         <-12.1 =C+ standard
>25.1 = C+ Standard         >18.1 = C+ Standard

In any case, did you try the formula suggested by Mike?

Bottom line, it works for the values you listed in your original post.

Now that you have a working structure, all you need to do is adapt it for whichever set of numbers you are actually working with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 11, 2012 at 00:55:37
Thank you it's working.

Report •

Ask Question