# 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 Standard3 to 10 = B Standard-7.1 to -12 = C Standard10.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

#1 July 10, 2012 at 06:48:21
 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")))MIKEhttp://www.skeptic.com/

Report •

#2 July 10, 2012 at 07:47:36
 -3 to 3 = A Standard3 to 10 = B StandardDo 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 StandardIIf(([T01] Between -3 And 5),"A",Example 2:-7.1 to -12 = C Standard10.1 to 18 = C StandardIIf(([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" Standard5.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 • 