Concatenate results of scores and return sum of percentages

[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

 

[JBeaucaire]

Without trying to figure out the dynamics of your 27 permutations, let's
just say YOU understand it. If that's the case, change your flags from 1
and 0 which EXCEL will instinctively fight with you over.

Use A and B, and for (null) you should designate a character, too,
perhaps a dash.

Now, forget needing fancy formulas. If you understand what the 27
permutations mean numerically, just make a chart with your 27 codes and
27 "results", then use a simple *=VLOOKUP(T2&U2&V2,$Y$2:$Z$26,2,FALSE)*
to pull the results over using a concatenated lookup.

I've attached a meager sample, you'll need to identify 27 unique
combinations of A, B and dash to create your codes, and fill in the
actual total.

As long as you include the FALSE flag, it will find exact matches from
your chart.


+-------------------------------------------------------------------+
|Filename: Permutations.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=53|
+-------------------------------------------------------------------+

 

[Ron Rosenfeld]

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

This is a Weighted Average problem where you want to ignore NULLS.

Excel 2007: =WEIGHTED.AVERAGE(A2:C2,$A$1:$C$1)*100

Prior versions do not have that function, so you can use this array-formula
(entered with <ctrl><shift><enter>):

=SUMPRODUCT($A$1:$C$1,A2:C2)/SUM(IF(LEN(A2:C2)>0,$A$1:$C$1))*100

Since you are displaying integers for results, you may want to ROUND the above
results:

=ROUND(SUMPRODUCT($A$1:$C$1,A2:C2)/SUM(IF(LEN(A2:C2)>0,$A$1:$C$1))*100,0)
--ron

 

[philiph]

Without trying to figure out <snip>

Thanks for your prompt reply JB!
Unfortunately, the flags must be as shown (at least for now). I was
interested in your comments though, and hadn't actually thought about
concatenating WITHIN a vlookup (duh, why not! :] ).

Ron's formula does what I need, thanks Ron, so I'll use that.

Thanks for the prompt reply both of you.

Phil

 

[Ron Rosenfeld]

Without trying to figure out <snip>

Thanks for your prompt reply JB!
Unfortunately, the flags must be as shown (at least for now). I was
interested in your comments though, and hadn't actually thought about
concatenating WITHIN a vlookup (duh, why not! :] ).

Ron's formula does what I need, thanks Ron, so I'll use that.

Thanks for the prompt reply both of you.

Phil

You're welcome. Glad to help.
--ron