re-indexing increments

[Calimer06]

Hello - this is my first post on this forum !

I might not be an excel Master, but I do work a lot with it and on pretty complex formulas, but on this one I feel stuck, close to a solution that eludes me everytime .

I have attached a file with the data and the desired outcome. Basically I have increments based on a unit in the laid out form and I would like to have them in a compact form (precisely this one ... unfortunatly).

Maybe someone here could have a look and give me a second opinion ?

Thank you very much

 

[markmzz]

Hello guys!

Thank you for your answer
i'm also (mostly) look at a formula to enter in M3 to calculate increments like this, and that I could drag on afterwards; so far im using an index formula with a small formula but I cant seem to get perfectly to win I want.

Do you have any ideas there ?

Thanks again

Sorry, but I didn’t folow you. Could you explain better?

Markmzz

 

[Calimer06]

It is indeed changing at every non-blank cell

 

[Calimer06]

Well you gave me formulas to retrieve the right increment for each unit - which is great ! But my biggest problem is how to retrieve the unit increments (so the column before M) to have the brackets exactly as is.

I don't know if it is doable

Really appreciate your help guys !

 

[sheetspread]

Excel 2010
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	From	to	Test 1		From	to	Test 2		From	to	Test 3		From	to	Test 4
3	0	0.5	11.76		0	0.5	13.45		0	0.5	17.57		0	0.5	16.72
4	0.5	1	0		0.5	1	4.14		0.5	1	3.86		0.5	1	1.71
5	1	3	0.87		1	1.5	3.44		1	3	3.32		1	2.5	1.73
6	3	5.5	0.99		1.5	3	2.75		3	35	4.41		2.5	3	0.96
7	5.5	11	1.24		3	5.5	1.39		35	200	4.28		3	5.5	0.94
8	11	21	1.59		5.5	21	2.34		200	300	4.35		5.5	11	0.76
9	21	35	1.52		21	35	2.07						11	21	0.77
10					35	300	1.98						21	35	0.65
11													35	80	1.06
Sheet2 (7)

Cell Formulas
Range	Formula
N2	="Test "&INT(COLUMN(B1)/4)+1
N3	{=INDEX(OFFSET($B$2:$B$54,0,INT(COLUMNS($Z$2:Z$54)/4)),SMALL(IF(OFFSET($B$2:$B$54,0,INT(COLUMNS($Z$2:Z$54)/4))<>"",ROW($B$2:$B$54)),ROW(A1))-1)}
L4	=M3
M3	{=INDEX($A$2:$A$54,SMALL(IF(OFFSET($B$2:$B$54,0,INT(COLUMNS($A$2:A$54)/4))<>"",ROW($B$2:$B$54)),ROW(A1))-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Drag L4 down, then M3:N3. Copy L2:N9, paste it every 2nd available column (P2:R9...etc), dragging the last row down until you get NUM errors. You see that the following row's lower bound is just the previous row's upper bound, starting from zero. Adjusting that to always start at the next blank cell is possible but gets really complicated.

 

[markmzz]

Hello!

Maybe this:

In N2:
="Test "&CEILING(COLUMNS($L$2:N$2),4)/4

In L3 and copy down until L13:
=IF(OR(M2="",M2=$A$55),"",IF(L2="From",0,M2))

In M3 and copy down until M13 (Array Formula - use Ctrl+Shift+Enter to enter the formula):
=IF(OR(M2="",M2=$A$55),"",IFERROR(INDEX($A$3:$A$55,
SMALL(IF(INDEX($B$3:$I$55,,MATCH(N$2,$B$2:$I$2,0))<>"",ROW($B$3:$B$55)-ROW($B$3)),ROWS($M$3:$M3)+1)),$A$55))

In N3 and copy down until N13:
=IF(M3="","",SUM(INDEX(($A$3:$A$55>L3)*($A$3:$A$55<=M3)*$B$3:$I$55,,MATCH(N$2,$B$2:$I$2,0))))

Then select L2:N13 and copy to the right.

Markmzz

 

[Calimer06]

sheetspread,

thank you again for your anwer - the formulas for unit increment isn't giving the right data.
For instance for "test 1" the first 0 increment should go from 0.5 to 2.5 - as the next increment is between 2.5 and 5 (0.87). Is that what you were talking about when you say adjusting makes thing very complicated ?

 

[Calimer06]

Hello !

This is super close, I just cant get the formula in N3 to work - I get a value error coming from the SUM - are you sure i can multiply the array of TRUE/FALSE (($A$3:$A$55>L3)*($A$3:$A$55<=M3)) by the whole matrix $B$3:$I$55 ?

Anyways, the units increment are beautiful

Thank you !

 

[markmzz]

Hello !

This is super close, I just cant get the formula in N3 to work - I get a value error coming from the SUM - are you sure i can multiply the array of TRUE/FALSE (($A$3:$A$55>L3)*($A$3:$A$55<=M3)) by the whole matrix $B$3:$I$55 ?

Anyways, the units increment are beautiful

Thank you !

Hi!

Here all is ok. Look at this:

[TABLE="class: grid, width: 1332"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Data[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="colspan: 2, align: center"]Desired outcome[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unit[/TD]
[TD]Test 1[/TD]
[TD]Test 2[/TD]
[TD]Test 3[/TD]
[TD]Test 4[/TD]
[TD]Test 5[/TD]
[TD]Test 6[/TD]
[TD]Test 7[/TD]
[TD]Test 8[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]Test 1[/TD]
[TD]
[/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]Test 2[/TD]
[TD]
[/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]Test 3[/TD]
[TD]
[/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]Test 4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]0,5[/TD]
[TD]11,76[/TD]
[TD]13,45[/TD]
[TD]17,57[/TD]
[TD]16,72[/TD]
[TD]19,1[/TD]
[TD]18,81[/TD]
[TD]24,53[/TD]
[TD]23,11[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0[/TD]
[TD]0,5[/TD]
[TD="align: right"]11,76[/TD]
[TD]
[/TD]
[TD]0[/TD]
[TD]0,5[/TD]
[TD="align: right"]13,45[/TD]
[TD]
[/TD]
[TD]0[/TD]
[TD]0,5[/TD]
[TD="align: right"]17,57[/TD]
[TD]
[/TD]
[TD]0[/TD]
[TD]0,5[/TD]
[TD="align: right"]16,72[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4,14[/TD]
[TD]3,86[/TD]
[TD]1,71[/TD]
[TD]2,17[/TD]
[TD]2,5[/TD]
[TD]5,74[/TD]
[TD]5,59[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0,5[/TD]
[TD]2,5[/TD]
[TD="align: right"]0[/TD]
[TD]
[/TD]
[TD]0,5[/TD]
[TD]1[/TD]
[TD="align: right"]4,14[/TD]
[TD]
[/TD]
[TD]0,5[/TD]
[TD]2,5[/TD]
[TD="align: right"]3,86[/TD]
[TD]
[/TD]
[TD]0,5[/TD]
[TD]2[/TD]
[TD="align: right"]1,71[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1,5[/TD]
[TD]
[/TD]
[TD]3,44[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1,49[/TD]
[TD]
[/TD]
[TD]3,92[/TD]
[TD]3,7[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2,5[/TD]
[TD]5[/TD]
[TD="align: right"]0,87[/TD]
[TD]
[/TD]
[TD]1[/TD]
[TD]2,5[/TD]
[TD="align: right"]3,44[/TD]
[TD]
[/TD]
[TD]2,5[/TD]
[TD]30[/TD]
[TD="align: right"]3,32[/TD]
[TD]
[/TD]
[TD]2[/TD]
[TD]2,5[/TD]
[TD="align: right"]1,73[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD="align: right"]0,99[/TD]
[TD]
[/TD]
[TD]2,5[/TD]
[TD]5[/TD]
[TD="align: right"]2,75[/TD]
[TD]
[/TD]
[TD]30[/TD]
[TD]100[/TD]
[TD="align: right"]4,41[/TD]
[TD]
[/TD]
[TD]2,5[/TD]
[TD]5[/TD]
[TD="align: right"]0,96[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]2,5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1,73[/TD]
[TD]0,8[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD="align: right"]1,24[/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD="align: right"]1,39[/TD]
[TD]
[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD="align: right"]4,28[/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD="align: right"]0,94[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]3[/TD]
[TD]0,87[/TD]
[TD]2,75[/TD]
[TD]3,32[/TD]
[TD]0,96[/TD]
[TD]0,88[/TD]
[TD]
[/TD]
[TD]2,88[/TD]
[TD]2,78[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD="align: right"]1,59[/TD]
[TD]
[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD="align: right"]2,34[/TD]
[TD]
[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD="align: right"]4,35[/TD]
[TD]
[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD="align: right"]0,76[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]3,5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]30[/TD]
[TD]300[/TD]
[TD="align: right"]1,52[/TD]
[TD]
[/TD]
[TD]30[/TD]
[TD]200[/TD]
[TD="align: right"]2,07[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD="align: right"]0,77[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD="align: right"]1,98[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]30[/TD]
[TD]70[/TD]
[TD="align: right"]0,65[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]4,5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]70[/TD]
[TD]300[/TD]
[TD="align: right"]1,06[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]5,5[/TD]
[TD]0,99[/TD]
[TD]1,39[/TD]
[TD]
[/TD]
[TD]0,94[/TD]
[TD]0,82[/TD]
[TD]2,1[/TD]
[TD]2,91[/TD]
[TD]2,96[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]29[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]Formulas[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]30[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]35[/TD]
[TD]1,52[/TD]
[TD]2,07[/TD]
[TD]4,41[/TD]
[TD]0,65[/TD]
[TD]0,91[/TD]
[TD]2,62[/TD]
[TD]3,16[/TD]
[TD]3,4[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]40[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]70[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD]80[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1,06[/TD]
[TD]1,7[/TD]
[TD]2,54[/TD]
[TD]3,36[/TD]
[TD]3,63[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD]90[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD]100[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD]200[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4,28[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3,26[/TD]
[TD]3,52[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD]300[/TD]
[TD]
[/TD]
[TD]1,98[/TD]
[TD]4,35[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2,43[/TD]
[TD]3,31[/TD]
[TD]3,58[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*****[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]********[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]********[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]********[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]********[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Calimer06]

I've copied exactly the formulas, I understand it also, but I still get a value error - im using excel 2016 - any idea ?

 

[Calimer06]

Actually I think I know what it is; in the excel i have, the blank cells are in fact "" - so they cannot be multiplied by the TRUE/FALSE array without returning #Value error