If , add , multiply , cell blank .

[Sunline]

Please ignore the lower EP2 formulars I didn’t want that in here , look at sheet only .

Hello all , I would like to get a basic function for the following ,
I don’t want to change any data shown here only a new function .
If range DH2:DI2 contains data then do nothing and return same number in cell W2 .
If range DH2:DI2 does not contain data in cell DH2 but DI2 and DJ2 both do , then halve the number in cell W2 and add the halved amount
too what number is in cell W2 giving a larger number from new function .
If range DH2:DI2 only contain data in only cell DJ2 , then multiply cell W2 by 3 times .
If range DH2:DI2 contains no data at all then zero 0 will always be returned .

I was really bad at school maths and needed tuition privately , im trying to give the cells with only one or two entries to keep up with the 3 ranged cells
Is it 1/2 or 33.3333 % to add . ??? .
Heck I hope this makes sence , my track records not good for explaining .
Thanks .

Excel Workbook
	V	W	X	Y	Z	DH	DI	DJ
1	Placing	Po	Rk	Po	Rk	PL3	PL2	PL1
2	1	155	1	155		8	8	3
3	2	125	5	125		5	4	8
4	3	146	2	146		4	5	4
5	4	15	8	45				14
6	5	10	10	30				13
7	6	24	6	72				8
8	7	131	4	131		6	4	11
9	8	0	13	0
10	9	144	3	216			3	7
11	10	0	13	0
12	11	16	7	16		7	11	9
13	12	0	13	0				15
14	13	2	11	3			11	11
15	14	1	12	3				11
16	15	13	9	19.5			10	14
Sheet1

 

[wangyuhu]

Y2=
{=choose(sum(if(dh2:dj2,{1,2,4},))+1,0,,,,w2*3,,w2*3/2,w2)}

ctrl+shift+enter

 

[Sunline]

Thanks wangyuhu , will give it a try out now .
thankyou .

 

[markmzz]

Sunline,

Only one question.

What is the expected result for the rows 18, 19, 21 and 22 in the sheet below?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>DH</th><th>DI</th><th>DJ</th><th>DK</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Placing</td><td style=";">Po</td><td style=";">Rk</td><td style=";">Po</td><td style=";">Rk</td><td style=";">PL3</td><td style=";">PL2</td><td style=";">PL1</td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">16</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: center;;">Case1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;background-color: #FFFF00;;">17</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;background-color: #FFFF00;;">18</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">19</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: center;;">Case2</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">20</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: center;;">Case3</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;background-color: #FFFF00;;">21</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;background-color: #FFFF00;;">22</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">23</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Case4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
Markmzz

 

[Sunline]

Hi , the function is working great but now getting #Value! error , because i forgot to advise you that in col range DH2:DJ2 there is also text as well as numbers , examples here of these .
B D F LR P RO U 1= 2= 3= 4= up too and including 17= .
Y2=
{=choose(sum(if(dh2:dj2,{1,2,4},))+1,0,,,,w2*3,,w2*3/2,w2)}
ctrl+shift+enter

Hello Mark , looking at your sample sheet expected results would be ,
18 , cell DJ18 would never be blank if there were only two entries in the range DH:DJ the data always starts in col DJ and moves left .
19 , same as above DI19 will never be blank .
21 , return answer is 3
22 , again DJ22 will never be blank .

Theses are only a sample range DH:DJ for posting purposes .
The full range i have data in is CG:DJ , 30 cols , so if there were 25 entries the first would start in col DJ moving left every cell would contain data up too and including col CL , CK:CG would all be blank .

Thanks .

 

[markmzz]

Sunline,

Sorry, one row is wrong.

The correct rows are: 18, 19, 22 and 23.

What is the expected result for the row 23.

DI23 and DJ23 will never be blank too?

Markmzz

 

[Sunline]

Hello Mark , yes thats right .
"DI23 and DJ23 will never be blank too?"

Row 23 , the number 1 would not be there , it cant as per how my sheet works , it would of been in cell DJ23 and if there was a second and third entry next cell to fill would be cols
DI then DH then DG DF DE and so on until last col for this information required being col CG .
All cols in the range CG:DJ could be blank but when one entry comes in it will always start in col DJ and backwords to col CG . There are never any gaps between filled cells . Thanks .

 

[markmzz]

Sunline,

Maybe this:

Note: you can choose Formula1 (DK2 - use helper name) or Formula2 (DL2 - use helper table).

Do some tests and tell me if it work.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>DH</th><th>DI</th><th>DJ</th><th>DK</th><th>DL</th><th>DM</th><th>DN</th><th>DO</th><th>DP</th><th>DQ</th><th>DR</th><th>DS</th><th>DT</th><th>DU</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Placing</td><td style=";">Po</td><td style=";">Rk</td><td style=";">Po</td><td style=";">Rk</td><td style=";">PL3</td><td style=";">PL2</td><td style=";">PL1</td><td style="background-color: #C5D9F1;;">Result with name</td><td style="background-color: #C5D9F1;;">Result with table</td><td style="background-color: #C5D9F1;;">={111\1;110\0;101\0;11\1.5;1\3;10\0;100\0;0\0}</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Helper table</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">155</td><td style="text-align: right;;">1</td><td style="text-align: right;;">155</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;background-color: #C5D9F1;;">155</td><td style="text-align: right;background-color: #C5D9F1;;">155</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">111</td><td style="text-align: center;background-color: #C5D9F1;;">1</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">125</td><td style="text-align: right;;">5</td><td style="text-align: right;;">125</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">8</td><td style="text-align: right;background-color: #C5D9F1;;">125</td><td style="text-align: right;background-color: #C5D9F1;;">125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">110</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">146</td><td style="text-align: right;;">2</td><td style="text-align: right;;">146</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;background-color: #C5D9F1;;">146</td><td style="text-align: right;background-color: #C5D9F1;;">146</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">101</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">15</td><td style="text-align: right;;">8</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;background-color: #C5D9F1;;">45</td><td style="text-align: right;background-color: #C5D9F1;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">11</td><td style="text-align: center;background-color: #C5D9F1;;">1.5</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">13</td><td style="text-align: right;background-color: #C5D9F1;;">30</td><td style="text-align: right;background-color: #C5D9F1;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">1</td><td style="text-align: center;background-color: #C5D9F1;;">3</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">24</td><td style="text-align: right;;">6</td><td style="text-align: right;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;background-color: #C5D9F1;;">72</td><td style="text-align: right;background-color: #C5D9F1;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">10</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">131</td><td style="text-align: right;;">4</td><td style="text-align: right;;">131</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">11</td><td style="text-align: right;background-color: #C5D9F1;;">131</td><td style="text-align: right;background-color: #C5D9F1;;">131</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">100</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">??</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">144</td><td style="text-align: right;;">3</td><td style="text-align: right;;">216</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">7</td><td style="text-align: right;background-color: #C5D9F1;;">216</td><td style="text-align: right;background-color: #C5D9F1;;">216</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;;">16</td><td style="text-align: right;;">7</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">9</td><td style="text-align: right;background-color: #C5D9F1;;">16</td><td style="text-align: right;background-color: #C5D9F1;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">13</td><td style="text-align: right;;">2</td><td style="text-align: right;;">11</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;background-color: #C5D9F1;;">3</td><td style="text-align: right;background-color: #C5D9F1;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;background-color: #C5D9F1;;">3</td><td style="text-align: right;background-color: #C5D9F1;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">15</td><td style="text-align: right;;">13</td><td style="text-align: right;;">9</td><td style="text-align: right;;">19.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">14</td><td style="text-align: right;background-color: #C5D9F1;;">19.5</td><td style="text-align: right;background-color: #C5D9F1;;">19.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">16</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">111</td><td style="text-align: right;background-color: #C5D9F1;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;background-color: #FFFF00;;">17</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;background-color: #FFFF00;;">18</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">19</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">166.5</td><td style="text-align: right;background-color: #C5D9F1;;">166.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">20</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">333</td><td style="text-align: right;background-color: #C5D9F1;;">333</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;background-color: #FFFF00;;">21</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;background-color: #FFFF00;;">22</td><td style="text-align: right;background-color: #FFFF00;;">111</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">23</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">SheetSL</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">DK2</th><td style="text-align:left">=W2*VLOOKUP(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">DH2:DJ2<>""</font>)*{100,10,1}</font>),myAR,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">DL2</th><td style="text-align:left">=W2*VLOOKUP(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">DH2:DJ2<>""</font>)*{100,10,1}</font>),$DO$2:$DP$9,2,0</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myAR</th><td style="text-align:left">={111,1;110,0;101,0;11,1.5;1,3;10,0;100,0;0,0}</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Sunline]

Whew , good news Formula2 (DL2 - use helper table) works , got rid of the #Value! as well .

As the above function is halving col W if 2 cols contain data , if i wanted to only add 33.3% what would i change in your above function or helper table , just to give me some options to play with the numbers .

Not sure where to put ={111,1;110,0;101,0;11,1.5;1,3;10,0;100,0;0,0} to get this one to work .
Thanks .

 

[markmzz]

Whew , good news Formula2 (DL2 - use helper table) works , got rid of the #Value! as well .

As the above function is halving col W if 2 cols contain data , if i wanted to only add 33.3% what would i change in your above function or helper table , just to give me some options to play with the numbers .

Not sure where to put ={111,1;110,0;101,0;11,1.5;1,3;10,0;100,0;0,0} to get this one to work .
Thanks .

First an explanation about my previous post, the auxiliary table used in Formula2 (cell DL2) is the table of the range DO2:DP9. Already the cell contents DM1 (={111,1;110,0;101,0;11,1.5;1,3;10,0;100,0;0,0}) is the formula that should be used when creating the name myAR being used in Formula1 (cell DK2).

Second, if I understand correctly what you want with the 33.3%, then the modification must be made to the Case2. I.e. change the following:

Formula1 – only the formula of the name myAR should be changed to ={111,1;110,0;101,0;11,1.333;1,3;10,0;100,0;0,0}

Formula2 – only the fourth row of the table (Case2) should be changed of 11 1.5 to 11 1,333

Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>DH</th><th>DI</th><th>DJ</th><th>DK</th><th>DL</th><th>DM</th><th>DN</th><th>DO</th><th>DP</th><th>DQ</th><th>DR</th><th>DS</th><th>DT</th><th>DU</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Placing</td><td style=";">Po</td><td style=";">Rk</td><td style=";">Po</td><td style=";">Rk</td><td style=";">PL3</td><td style=";">PL2</td><td style=";">PL1</td><td style="background-color: #C5D9F1;;">Result with name</td><td style="background-color: #C5D9F1;;">Result with table</td><td style="font-weight: bold;background-color: #92D050;;">={111,1;110,0;101,0;11,1.333;1,3;10,0;100,0;0,0}</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Helper table</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PL3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PL2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PL1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">155</td><td style="text-align: right;;">1</td><td style="text-align: right;;">155</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;background-color: #C5D9F1;;">155</td><td style="text-align: right;background-color: #C5D9F1;;">155</td><td style=";">={111,1;110,0;101,0;11,1.5;1,3;10,0;100,0;0,0}</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">111</td><td style="text-align: center;background-color: #C5D9F1;;">1</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">125</td><td style="text-align: right;;">5</td><td style="text-align: right;;">125</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">8</td><td style="text-align: right;background-color: #C5D9F1;;">125</td><td style="text-align: right;background-color: #C5D9F1;;">125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">110</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">146</td><td style="text-align: right;;">2</td><td style="text-align: right;;">146</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;background-color: #C5D9F1;;">146</td><td style="text-align: right;background-color: #C5D9F1;;">146</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">If I understand correctly what you want,</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">101</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">15</td><td style="text-align: right;;">8</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;background-color: #C5D9F1;;">45</td><td style="text-align: right;background-color: #C5D9F1;;">45</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">you have to change in the Case2 of 1.5 to 1.333</td><td style="font-weight: bold;text-align: right;background-color: #92D050;;">Case2</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">11</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">1.333</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">13</td><td style="text-align: right;background-color: #C5D9F1;;">30</td><td style="text-align: right;background-color: #C5D9F1;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">1</td><td style="text-align: center;background-color: #C5D9F1;;">3</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">24</td><td style="text-align: right;;">6</td><td style="text-align: right;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;background-color: #C5D9F1;;">72</td><td style="text-align: right;background-color: #C5D9F1;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">10</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">131</td><td style="text-align: right;;">4</td><td style="text-align: right;;">131</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">11</td><td style="text-align: right;background-color: #C5D9F1;;">131</td><td style="text-align: right;background-color: #C5D9F1;;">131</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">100</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: center;background-color: #C5D9F1;;">0</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">144</td><td style="text-align: right;;">3</td><td style="text-align: right;;">216</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">7</td><td style="text-align: right;background-color: #C5D9F1;;">191.952</td><td style="text-align: right;background-color: #C5D9F1;;">191.952</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;;">16</td><td style="text-align: right;;">7</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">9</td><td style="text-align: right;background-color: #C5D9F1;;">16</td><td style="text-align: right;background-color: #C5D9F1;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">13</td><td style="text-align: right;;">2</td><td style="text-align: right;;">11</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;background-color: #C5D9F1;;">2.666</td><td style="text-align: right;background-color: #C5D9F1;;">2.666</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;background-color: #C5D9F1;;">3</td><td style="text-align: right;background-color: #C5D9F1;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">15</td><td style="text-align: right;;">13</td><td style="text-align: right;;">9</td><td style="text-align: right;;">19.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">14</td><td style="text-align: right;background-color: #C5D9F1;;">17.329</td><td style="text-align: right;background-color: #C5D9F1;;">17.329</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">16</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">111</td><td style="text-align: right;background-color: #C5D9F1;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">17</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">18</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">19</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">147.963</td><td style="text-align: right;background-color: #C5D9F1;;">147.963</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">20</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #C5D9F1;;">333</td><td style="text-align: right;background-color: #C5D9F1;;">333</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">21</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">22</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">23</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;background-color: #C5D9F1;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">SheetSL</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">DK2</th><td style="text-align:left">=W2*VLOOKUP(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">DH2:DJ2<>""</font>)*{100,10,1}</font>),myAR,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">DL2</th><td style="text-align:left">=W2*VLOOKUP(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">DH2:DJ2<>""</font>)*{100,10,1}</font>),$DO$2:$DP$9,2,0</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myAR</th><td style="text-align:left">={111,1;110,0;101,0;11,1.333;1,3;10,0;100,0;0,0}</td></tr></tbody></table></td></tr></table><br />
If I'm wrong, so tell me which case I have to change.

Markmzz