Solved: Help with IF statements in Excel

[Beaver]

Ok I really suck at this. I am trying to read through chemistry data and assign qualities based on the numbers. Below is what I am working with. It seemed easy once I wrote it out, but again, I suck. Any help would be greatly appreciated. I am sure an Excel wiz could bust it out in no time.

working with only one column. (M)

IF


M2<.14= BAD <---output IF M2 is less than .14 it is BAD

.141<M2>.175= NP IF M2 is between .141 and .175 it is NP

.1751<M2>.1899= PT IF M2 is between .1751 and .1899 it is PT

.190<M2>.1999= BAD IF M2 is between .190 and .1999 it is BAD

.20<M2>.24= H IF M2 is between .20 and .24 it is H

.241<M2= BAD IF M2 is greater than .241 it is BAD


SAMPLE DATA
M2
0.1345
0.1829
0.1930
0.1811
0.1848
0.1750
0.1847
0.1921
0.1806
0.1910
0.1422
0.1604
0.1335
0.1570
0.1737
0.1895
0.1911
0.1740
0.1983
0.1982
0.1621
0.2122
0.2481

[Beaver]

Second spreadsheet. A little more difficult, working with 4 columns.

Think I have the formulas correct, if not the written description is correct.

IF

.06<G2>.10 AND J2>.01 AND I2>.05 AND .181<P2<.19 = C ELSE is BAD

G2 must be between .10 and .06 AND J2 must be greater than .01 AND I2 must be greater than .05 AND P2 must be between .181 and .19 to be GOOD

.06<G2>.10 AND .01>J2>.06 AND 0<I2<.09 AND .181<P2<.19 = NC ELSE is BAD

G2 must be between .10 and .06 AND J2 must be between .01 and .06 AND I2 must be between 0 and .09 AND P2 must be between .181 and .19 to be GOOD

.181<P2<.19 = TP


SAMPLE DATA

G2

0.0842
0.0842
0.0813
0.0529
0.0862
0.0842
0.0905
0.0865
0.0892
0.0947
0.0855
0.0937
0.1011

J2

0.0113
0.0109
0.0117
0.0119
0.0132
0.0130
0.0144
0.0146
0.0132
0.0143
0.0127
0.0091

I2

0.0468
0.0464
0.0587
0.0604
0.0594
0.0576
0.0502
0.0419
0.0435
0.0982
0.0473

P2

0.1809
0.1799
0.1764
0.1774
0.1820
0.1903
0.1729
0.1474
0.1798
0.1617
0.1682

[Bob Phillips]

Do YOU have a workbook with data and expected results?

[MaximS]

I've made small macro which will add in corresponding collumn N cell certain value ie. "BAD", "PT" dependant on value in column M.

I won't do anything if value is not numeric or blank.

It will process only when value was changed.

see attachement for details.

If its about the second part can you do it in the same way as first part and review it.

[Beaver]

Do YOU have a workbook with data and expected results?
YES

[Beaver]

I've made small macro which will add in corresponding collumn N cell certain value ie. "BAD", "PT" dependant on value in column M.

I won't do anything if value is not numeric or blank.

It will process only when value was changed.

see attachement for details.

If its about the second part can you do it in the same way as first part and review it.

Thanks for that. I created a Macro within my spreadsheet, but it won't let me run it. I get a error box stating "Argument not optional" What is that? and Why/

[MaximS]

Can you post your spreadsheet ?? It's pretty hard to answer your question without seeing the code and content of the file.

[Beaver]

Excel spreadsheet I am working with.

[Bob Phillips]

What results are you expecting, I can't figure it out?

[rbrhodes]

Beaver,

Here's the IF statement for your first example:

=IF(M2<0.141,"BAD",IF(M2<0.1751,"NP",IF(M2<0.19,"PT",IF(M2<0.2,"BAD",IF(M2<0.241,"H","BAD")))))

It simply checks each boundary condition in sequence from smallest to largest. In other words if M2 is not less than .14 it checks to see if M2 is less than .1751, then if M2 is less than .19, etc. Any test that answers True puts the answer between the previously tested numbers.


For example: .173 when tested is as follows

Less than .14 = FALSE

Less than .1751 = TRUE

So must be between .141 and .175.

Make sense?

Here's your second query:

=IF(AND(AND(AND(AND(G7>=0.06,G7<=0.1),I7>0.05),J7>0.01),AND(P7>=0.181,P7<=0.19)),"C","BAD")

and the third:

=IF(AND(AND(AND(AND(G8>=0.06,G8<=0.1),AND(I8>=0,I8<=0.09)),AND(J8>=0.01,J8<=0.06)),AND(P8>=0.181,P8<=0.19)),"NC","BAD")


The secret to building these is to start with the IF, then add the internal AND's, and finally the external AND's.

By internal I mean: G2=>.06 AND G2 <=.10 Actually written as:

AND(G2=>.06,G2 <=.10)

By external I mean: I2>.05 AND J2>.01 Actually written as:

AND(AND(AND(G7>=0.06,G7<=0.1),I7>0.05),J7>0.01)

Still with me?



This could be done with VBA as well but (to me at least) the formulas are easier.

[Paul_Hossler]

Couple of comments and suggestions

if the value is 0.17505 (for ex) you'll fall through your Cases


Case 0.141 To 0.175
Case 0.1751 To 0.1899
Case 0.19 To 0.1999
Case 0.2 To 0.24
Case Else



My preferance (personal style) would be to use a User Defined Function, since I get confused if I have a long worksheet formula with lots of nested IF's and AND's etc.

You could pass the value, and the thresholds for the different 'answers'

Example:


'Sample
' =WhatIsIt(M2, 0.141, 0.1751, 0.19, 0.2, 0.24)

Function WhatIsIt(ValueIn As Double, NP_Low As Double, PT_Low As Double, BAD_Low As Double, H_Low As Double, H_High As Double) As String

If ValueIn < NP_Low Then
WhatIsIt = "BAD"

ElseIf ValueIn < PT_Low Then
WhatIsIt = "NP"

ElseIf ValueIn < BAD_Low Then
WhatIsIt = "PT"

ElseIf ValueIn < H_Low Then
WhatIsIt = "BAD"

ElseIf ValueIn < H_High Then
WhatIsIt = "H"

Else
WhatIsIt = "BAD"
End If
End Function


Paul

[Beaver]

Thanks for this information. I was able to figure the first query out last night. My "in between" approach wasn't working. Now for my second statement. I will be reading only one set of data in one spreadsheet. Is there a way to combine the IF statements for query 2/3 into one? Read all the data and spit out either C, NC, or BAD. If not I will create another spreadsheet. One for C and another for NC.



Beaver,

Here's the IF statement for your first example:

=IF(M2<0.141,"BAD",IF(M2<0.1751,"NP",IF(M2<0.19,"PT",IF(M2<0.2,"BAD",IF(M2<0.241,"H","BAD")))))

It simply checks each boundary condition in sequence from smallest to largest. In other words if M2 is not less than .14 it checks to see if M2 is less than .1751, then if M2 is less than .19, etc. Any test that answers True puts the answer between the previously tested numbers.


For example: .173 when tested is as follows

Less than .14 = FALSE

Less than .1751 = TRUE

So must be between .141 and .175.

Make sense?

Here's your second query:

=IF(AND(AND(AND(AND(G7>=0.06,G7<=0.1),I7>0.05),J7>0.01),AND(P7>=0.181,P7<=0.19)),"C","BAD")

and the third:

=IF(AND(AND(AND(AND(G8>=0.06,G8<=0.1),AND(I8>=0,I8<=0.09)),AND(J8>=0.01,J8<=0.06)),AND(P8>=0.181,P8<=0.19)),"NC","BAD")


The secret to building these is to start with the IF, then add the internal AND's, and finally the external AND's.

By internal I mean: G2=>.06 AND G2 <=.10 Actually written as:

AND(G2=>.06,G2 <=.10)

By external I mean: I2>.05 AND J2>.01 Actually written as:

AND(AND(AND(G7>=0.06,G7<=0.1),I7>0.05),J7>0.01)

Still with me?



This could be done with VBA as well but (to me at least) the formulas are easier.

[rbrhodes]

Here you are:

=IF(AND(AND(AND(AND(G7>=0.06,G7<=0.1),AND(I7>=0,I7<=0.09)),AND(J7>=0.01,J7<=0.06)),AND(P7>=0.181,P7<=0.19)),"NC",IF(AND(AND(AND(AND(G7>=0.06,G7<=0.1),I7>=0.05),J7>=0.01),AND(P7>=0.181,P7<=0.19)),"C","BAD"))

It does the first test and if TRUE echos "NC" if false it does the second test, echoing "C" if true and "BAD" if the test fails

[Beaver]

Thats it. AGAIN, thanks a lot for your help. The internets are awesome!