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&N1 1="",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

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|
+-------------------------------------------------------------------+

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44694

On Sun, 28 Dec 2008 16:29:19 -0800 (PST), wrote:

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&N 11="",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

On Dec 29, 3:43*pm, JBeaucaire
wrote:
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

On Mon, 29 Dec 2008 12:09:54 -0800 (PST), wrote:

On Dec 29, 3:43*pm, JBeaucaire
wrote:
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