P
philiph
I have 3 columns. Rows 2 to 20 can have either "1", "0", or
"" (blank).
ColT ColU ColV
15% 20% 65%
1 1 1
1 1 1
1 1
1 0 1
1 1 1
1 1 1
I need a result in ColW based on the 27 permutations available:
15% 20% 65%
1 1 1 = 100 (15+20+65)
1 1 0 = 35 (15+20 'cos C3 is 0)
1 1 = 100 ((15+20)/(15+20), C4 is not applicable)
1 0 1 = 80 (15+65 'cos B5 is 0)
1 0 0 = 15
1 0 = 43 (15/(15+20) , C7 not applicable)
1 1 = 100
1 0 = 19 (15/(15+65))
1 = 100 (15/15)
0 1 1 = 85
0 1 0 = 20
0 1 = 57
0 0 1 = 65
0 0 0 = 0
0 0 = 0
0 1 = 81 (65/(15+65))
0 0 = 0
0 = 0
1 1 = 100
1 0 = 24
1 = 100
0 1 = 76
0 0 = 0
0 = 0
1 = 100
0 = 0
(this row is 3 blank cells and will return "" with =if
(a2="","",if(....your formula...) where colA contains names.
I've been trying:
=IF(COUNTIF(T13:V13,0)=0,1,IF(VALUE(T13&U13)=11,0.35,IF(VALUE(T13&V13)
=11,0.8,IF(VALUE(U13&V13)=11,0.85,"do I have to keep adding nested IF
statements!!??"))))
but there MUST be a more elegant solution and doesn't involve nested
IF's.
I have the same situation in another set of columns but with only two
col's I was able to use
=IF(B11="","",IF(M11&N11="11",1,IF(M11&N11="10",M$10,IF(M11&N11="1",
1,IF(M11&N11="01",N$10,IF(M11&N11="00",0,IF(M11&N11="",1,0)))))))
which is really unwieldy for 3 columns.
Any suggestions? If the formula could reference the percentages in row
1, then the results would reflect any changes we made to those
figures...
Phil
"" (blank).
ColT ColU ColV
15% 20% 65%
1 1 1
1 1 1
1 1
1 0 1
1 1 1
1 1 1
I need a result in ColW based on the 27 permutations available:
15% 20% 65%
1 1 1 = 100 (15+20+65)
1 1 0 = 35 (15+20 'cos C3 is 0)
1 1 = 100 ((15+20)/(15+20), C4 is not applicable)
1 0 1 = 80 (15+65 'cos B5 is 0)
1 0 0 = 15
1 0 = 43 (15/(15+20) , C7 not applicable)
1 1 = 100
1 0 = 19 (15/(15+65))
1 = 100 (15/15)
0 1 1 = 85
0 1 0 = 20
0 1 = 57
0 0 1 = 65
0 0 0 = 0
0 0 = 0
0 1 = 81 (65/(15+65))
0 0 = 0
0 = 0
1 1 = 100
1 0 = 24
1 = 100
0 1 = 76
0 0 = 0
0 = 0
1 = 100
0 = 0
(this row is 3 blank cells and will return "" with =if
(a2="","",if(....your formula...) where colA contains names.
I've been trying:
=IF(COUNTIF(T13:V13,0)=0,1,IF(VALUE(T13&U13)=11,0.35,IF(VALUE(T13&V13)
=11,0.8,IF(VALUE(U13&V13)=11,0.85,"do I have to keep adding nested IF
statements!!??"))))
but there MUST be a more elegant solution and doesn't involve nested
IF's.
I have the same situation in another set of columns but with only two
col's I was able to use
=IF(B11="","",IF(M11&N11="11",1,IF(M11&N11="10",M$10,IF(M11&N11="1",
1,IF(M11&N11="01",N$10,IF(M11&N11="00",0,IF(M11&N11="",1,0)))))))
which is really unwieldy for 3 columns.
Any suggestions? If the formula could reference the percentages in row
1, then the results would reflect any changes we made to those
figures...
Phil