| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Can it be done
I need to evaluate the contents of three cells and return one of 8
choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Thanks in advance for any thoughts or suggestions. Regards, Luke |
|
#2
|
|||
|
|||
|
"Luke Dallman" wrote...
I need to evaluate the contents of three cells and return one of 8 choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Divide & conquer. High/Low R completely determines 1 or 2 and is independent of a/b/c/d. That just leaves 4 letters. =IF(R=R_Threshold,1,2)&IF(HR=HR_Threshold, IF(Pct=Pct_Threshold),"a","b"),IF(Pct=Pct_Thresh old),"c","d")) If this were more complicated, you could use LOOKUP. =LOOKUP((R=R_Threshold)+2*(HR=HR_Threshold)+4*(P ct=Pct_Threshold), {0;1;2;3;4;5;6;7},{"1a";"1b";"1c";"1d";"2a";"2b";" 2c";"2d"}) |
|
#3
|
|||
|
|||
|
Hi!
Here's one way: Create a table that lists your criteria designations: 1a 1b 1c ... ... 2d In the adjacent column enter these formulas as they correspond to each criteria: =AND(A10=A2,A10=B2,A10=C2) =AND(A10=A2,A10=B2,A10<C2) =AND(A10=A2,A10<B2,A10=C2) =AND(A10=A2,A10<B2,A10<C2) =AND(A10<A2,A10=B2,A10=C2) =AND(A10<A2,A10=B2,A10<C2) =AND(A10<A2,A10<B2,A10=C2) =AND(A10<A2,A10<B2,A10<C2) In this example I'm using A10 as the comparison cell and A2,B2 and C2 to hold "R" , "HR" and "%", respectively. Now, to return "D" use this formula: =IF(OR(A10="",A2="",B2="",C2=""),"",INDEX(J2:J9,MA TCH (TRUE,I2:I9,0))) Whe J2:J9 = criteria designations I2:I9 = corresponding criteria formula Biff -----Original Message----- I n1ceed to evaluate the contents of three cells and return one of 8 choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Thanks in advance for any thoughts or suggestions. Regards, Luke . |
|
#4
|
|||
|
|||
|
Just an option to try ..
Assuming the reference table below is in Sheet1, A1:D8 (i.e. w/o the dashes and the commas) 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % In Sheet2 ------------- Assuming you have other formulas (in cols A to C, row2 down) which evaluate to these sample text strings: high R high HR high % high R high HR low % high R low HR high % etc Put in D2: =IF(ISNA(MATCH(TRIM(A2&B2&C2),TRIM(Sheet1!$B$1:$B$ 8&Sheet1!$C$1:$C$8&Sheet1! $D$1:$D$8),0)),"",INDEX(Sheet1!$A$1:$A$8,MATCH(TRI M(A2&B2&C2),TRIM(Sheet1!$B $1:$B$8&Sheet1!$C$1:$C$8&Sheet1!$D$1:$D$8),0))) Array-enter the formula in D2, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER Copy D2 down For the sample data shown, you'll get the desired results returned in col D: high R high HR high % 1a high R high HR low % 1b high R low HR high % 1c Any unmatched text string concats will return blanks: "" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Luke Dallman" wrote in message .. . I need to evaluate the contents of three cells and return one of 8 choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Thanks in advance for any thoughts or suggestions. Regards, Luke |
|
#5
|
|||
|
|||
|
Hi!
In my example I'm comparing A10 to A2, B2 and C2. After seeing Harlan's post and rereading the original post, I think my comparison is backwards. Should compare A2, B2 and C2 to A10. Harlan's solution is more efficient and you should probably use it, although you may want to add to it to account for any blank cells. Biff -----Original Message----- Hi! Here's one way: Create a table that lists your criteria designations: 1a 1b 1c ... ... 2d In the adjacent column enter these formulas as they correspond to each criteria: =AND(A10=A2,A10=B2,A10=C2) =AND(A10=A2,A10=B2,A10<C2) =AND(A10=A2,A10<B2,A10=C2) =AND(A10=A2,A10<B2,A10<C2) =AND(A10<A2,A10=B2,A10=C2) =AND(A10<A2,A10=B2,A10<C2) =AND(A10<A2,A10<B2,A10=C2) =AND(A10<A2,A10<B2,A10<C2) In this example I'm using A10 as the comparison cell and A2,B2 and C2 to hold "R" , "HR" and "%", respectively. Now, to return "D" use this formula: =IF(OR(A10="",A2="",B2="",C2=""),"",INDEX(J2:J9,M ATCH (TRUE,I2:I9,0))) Whe J2:J9 = criteria designations I2:I9 = corresponding criteria formula Biff -----Original Message----- I n1ceed to evaluate the contents of three cells and return one of 8 choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Thanks in advance for any thoughts or suggestions. Regards, Luke . . |
|
#6
|
|||
|
|||
|
Hi
With conditions in cells A1:C1 =MATCH(A1,{"high R";"low R"},0) & CHOOSE(MATCH(B1&C1,{"high HRhigh %";"high HRlow %";"low HRhigh %";"low HRlow %"};0),"a","b","c","d") -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Luke Dallman" wrote in message .. . I need to evaluate the contents of three cells and return one of 8 choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Thanks in advance for any thoughts or suggestions. Regards, Luke |
|
#7
|
|||
|
|||
|
Extending the earlier suggestion a little further ..:
Presuming MyTable is a defined range referring to a 10 R x 4 C grid which contains the definitions/rules (say): 0 low R high HR low % 1 low R high HR low % 2 low R high HR low % 3 low R high HR low % 4 low R high HR low % 5 high R low HR high % 6 high R low HR high % 7 high R low HR high % 8 high R low HR high % 9 high R low HR high % Then, if you have the source numbers for R, HR and % in cols A to C, row2 down, viz.: R HR % D 5 8 8 ?? etc you could put in D2: =IF(ISERROR(MATCH(TRIM(VLOOKUP(A2,MyTable,2)&VLOOK UP(B2,MyTable,3)&VLOOKUP(C 2,MyTable,4)),TRIM(Sheet1!$B$1:$B$8&Sheet1!$C$1:$C $8&Sheet1!$D$1:$D$8),0))," ",INDEX(Sheet1!$A$1:$A$8,MATCH(TRIM(VLOOKUP(A2,MyT able,2)&VLOOKUP(B2,MyTable ,3)&VLOOKUP(C2,MyTable,4)),TRIM(Sheet1!$B$1:$B$8&S heet1!$C$1:$C$8&Sheet1!$D$ 1:$D$8),0))) Array-enter the formula in D2, and fill down Col D will return: 1c, etc depending on the source numbers in cols A to C -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
|
#8
|
|||
|
|||
|
It can be done:
=MID("1a1b1c1d2a2b2c2d",SUM((A2:C2A3:C3)*{8\4\2}) +1,2) Ola Sandstrom Hold down Ctrl and Shift, then hit Enter to confirm the formula. A2:C2 is your reference numbers A3:C3 is your values |
|
#9
|
|||
|
|||
|
Sorry. You need to use
=MID("1a1b1c1d2a2b2c2d",SUM((A2:C2A3:C3)*{8,4,2}) +1,2) In Sweden we have to convert ;--, \--; . -- , Ola |
|
#10
|
|||
|
|||
|
"Luke Dallman" wrote in message .. . I need to evaluate the contents of three cells and return one of 8 choices in a fourth cell. The contents of the cells to be evaluated are numbers and are compared to a given number as either "<" or "=". The choices to return are 1a thru 1d or 2a thru 2d. R HR % D 5 8 8 1a etc. I have nested IF and AND functions, but can't figure out how to get past the 7 nested functions limitation. Instead of using nested IF's replace them with boolean statements which if tru resolve to 1 and if false resolve to 0. So (Calculation1*Condition1) + (Calculation2*condition2) + etc. This can thus do away with the 7 condition limit. Chris The formula works for 7 of the sets of conditions, but not the 8th. The criteria a 1a - high R, high HR, high % 1b - high R, high HR, low % 1c - high R, low HR, high % 1d - high R, low HR, low % 2a - low R, high HR, high % 2b - low R, high HR, low % 2c - low R, low HR, high % 2d - low R, low HR, low % Thanks in advance for any thoughts or suggestions. Regards, Luke |
|
#11
|
|||
|
|||
|
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
