re-indexing increments

Calimer06

New Member
Joined
Jan 25, 2018
Messages
12
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 :)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
[TABLE="width: 1216"]
<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 576, colspan: 9"]Data[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 192, colspan: 3"]Desired outcome[/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 256, colspan: 4"][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[TD="align: right"]11.76[/TD]
[TD="align: right"]13.45[/TD]
[TD="align: right"]17.57[/TD]
[TD="align: right"]16.72[/TD]
[TD="align: right"]19.1[/TD]
[TD="align: right"]18.81[/TD]
[TD="align: right"]24.53[/TD]
[TD="align: right"]23.11[/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]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.14[/TD]
[TD="align: right"]3.86[/TD]
[TD="align: right"]1.71[/TD]
[TD="align: right"]2.17[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]5.74[/TD]
[TD="align: right"]5.59[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]11.76[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]13.45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD][/TD]
[TD="align: right"]3.44[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.49[/TD]
[TD][/TD]
[TD="align: right"]3.92[/TD]
[TD="align: right"]3.7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4.14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.87[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]3.44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.73[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.99[/TD]
[TD][/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]2.75[/TD]
[TD="align: right"]3.32[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]0.88[/TD]
[TD][/TD]
[TD="align: right"]2.88[/TD]
[TD="align: right"]2.78[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.24[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1.59[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2.34[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1.52[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]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="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1.98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]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]
[/TR]
[TR]
[TD="align: right"]5.5[/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]1.39[/TD]
[TD][/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]0.82[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]2.91[/TD]
[TD="align: right"]2.96[/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"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]6.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]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]7.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]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]8.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]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]9.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]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]11[/TD]
[TD="align: right"]1.24[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.76[/TD]
[TD="align: right"]0.93[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]3.45[/TD]
[TD="align: right"]3.56[/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"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]21[/TD]
[TD="align: right"]1.59[/TD]
[TD="align: right"]2.34[/TD]
[TD][/TD]
[TD="align: right"]0.77[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]2.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]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]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]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]1.52[/TD]
[TD="align: right"]2.07[/TD]
[TD="align: right"]4.41[/TD]
[TD="align: right"]0.65[/TD]
[TD="align: right"]0.91[/TD]
[TD="align: right"]2.62[/TD]
[TD="align: right"]3.16[/TD]
[TD="align: right"]3.4[/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"]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]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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"]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]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.06[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]2.54[/TD]
[TD="align: right"]3.36[/TD]
[TD="align: right"]3.63[/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"]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]
[/TR]
[TR]
[TD="align: right"]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]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.26[/TD]
[TD="align: right"]3.52[/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"]300[/TD]
[TD][/TD]
[TD="align: right"]1.98[/TD]
[TD="align: right"]4.35[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.43[/TD]
[TD="align: right"]3.31[/TD]
[TD="align: right"]3.58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I have attached a file with the data and the desired outcome.
You cannot attach files on this forum... what you need to do is open an account on DropBox (unless you already have one), post the file there, mark it for sharing and then post a link to that file here so we can download it.
 
Upvote 0
Hello!

Try this formula in N4 and copy down:

Code:
=SUM(INDEX(($A$3:$A$55>L4)*($A$3:$A$55<=M4)*$B$3:$I$55,,MATCH(N$3,$B$2:$I$2,0)))

Markmzz
 
Upvote 0

Excel 2010
ABCDEFGHIJKLMNOPQR
1UnitTest 1Test 2Test 3Test 4Test 5Test 6Test 7Test 8
20.511.7613.4517.5716.7219.118.8124.5323.11FromtoTest 1fromtoTest 2
3104.143.861.712.172.55.745.5900.511.7600.513.45
41.53.441.493.923.70.52.500.514.14
522.550.8712.53.44
62.51.730.85100.992.552.75
730.872.753.320.960.882.882.7810201.245201.39
83.520301.5920302.34
94303001.52302002.07
104.52003001.98
115
125.50.991.390.940.822.12.912.96
136
146.5
157
167.5
178
188.5
199
209.5
2110
22111.240.760.932.53.453.56
2312
2413
2514
2615
2716
2817
2918
3019
3120
32211.592.340.770.962.52
3322
3423
3524
3625
3726
3827
3928
4029
4130
42351.522.074.410.650.912.623.163.4
4340
4445
4550
4655
4760
4865
4970
50801.061.72.543.363.63
5190
52100
532004.283.263.52
543001.984.352.433.313.58
Sheet1 (2)
Cell Formulas
RangeFormula
N3=SUMPRODUCT(--($A$2:$A$54>L3),--($A$2:$A$54<=M3),$B$2:$B$54)
R3=SUMPRODUCT(--($A$2:$A$54>P3),--($A$2:$A$54<=Q3),$C$2:$C$54)
 
Upvote 0
Hello!

Try this formula in N4 and copy down:

Code:
=SUM(INDEX(($A$3:$A$55>L4)*($A$3:$A$55<=M4)*$B$3:$I$55,,MATCH(N$3,$B$2:$I$2,0)))

Markmzz

By the way, you can copy the formula of the cell N4 to the cell R4 and then copy down.

[TABLE="class: grid, width: 913"]
<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]
[/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]
[/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]From[/TD]
[TD]to[/TD]
[TD]Test 1[/TD]
[TD][/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]Test 2[/TD]
[TD][/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[/TD]
[TD]0,5[/TD]
[TD="align: right"]11,76[/TD]
[TD]11,76[/TD]
[TD]0[/TD]
[TD]0,5[/TD]
[TD="align: right"]13,45[/TD]
[TD]13,45[/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]0,5[/TD]
[TD]2,5[/TD]
[TD="align: right"]0[/TD]
[TD]0[/TD]
[TD]0,5[/TD]
[TD]1[/TD]
[TD="align: right"]4,14[/TD]
[TD]4,14[/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]2,5[/TD]
[TD]5[/TD]
[TD="align: right"]0,87[/TD]
[TD]0,87[/TD]
[TD]1[/TD]
[TD]2,5[/TD]
[TD="align: right"]3,44[/TD]
[TD]3,44[/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]5[/TD]
[TD]10[/TD]
[TD="align: right"]0,99[/TD]
[TD]0,99[/TD]
[TD]2,5[/TD]
[TD]5[/TD]
[TD="align: right"]2,75[/TD]
[TD]2,75[/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]10[/TD]
[TD]20[/TD]
[TD="align: right"]1,24[/TD]
[TD]1,24[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD="align: right"]1,39[/TD]
[TD]1,39[/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]20[/TD]
[TD]30[/TD]
[TD="align: right"]1,59[/TD]
[TD]1,59[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD="align: right"]2,34[/TD]
[TD]2,34[/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]30[/TD]
[TD]300[/TD]
[TD="align: right"]1,52[/TD]
[TD]1,52[/TD]
[TD]30[/TD]
[TD]200[/TD]
[TD="align: right"]2,07[/TD]
[TD]2,07[/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]Formula[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD="align: right"]1,98[/TD]
[TD]1,98[/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]Formula[/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]
[/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][/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top