How to dynamically populate columns with the delta values

[jpravin]

I have a worksheet with rows containing the following columns

Start Value	Duration	End Value
1435	112	1547
702	431	1133

<tbody>
</tbody>

Now i want the data in the duration column(which is essentially in milliseconds(ms) to be represented as below

0-100	101-200	201-300	301-400	401-500	501-600	601-700	701-800	801-900	901-1000	1001-1100	1101-1200	1201-1300	1301-1400	1401-1500	1501-1600
														65	47
							98	100	100	100	33

<tbody>
</tbody>

which represents the difference as progress in time along the columns.

Please help me with how to get this in a separate sheet

thanks in advance

 

[cyrilbrd]

Hi,
Are the data divided randomly or is there a specific ratio to respect? Hence why 65 - 47 and not 100-12?

 

[jpravin]

The data (difference) is not randomly spread but as per logic as explained below

For eg in 1st row for 1435 (start),112(difference) and 1547(end)

In the x axis for time in milliseconds ie 0 100...... 1400, 1500,1600...
the difference value is to be represented as spread across in splits between each 100th milliseconds.

Hence the difference value that needs to split in time slots 1400-1500, 1501- 1600 would be 65 and 47 respectively.

Hope I cleared your doubt.

 

[cyrilbrd]

Not very elegant and certainly could be made faster:
Given in A1:

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Start Value	Duration	End Value
2	1435	112	1547
3	702	431	1133
4	56	234	290
5
6
7	0-100	101-200	201-300	301-400	401-500	501-600	601-700	701-800	801-900	901-1000	1001-1100	1101-1200	1201-1300	1301-1400	1401-1500	1501-1600
8															65	47
9								98	100	100	100	33
10	44	100	90
jpravin

Formula in A8 is =IF(AND($A2 > = TRIM(MID(SUBSTITUTE(A$7,"-",REPT(" ",19)),1*19-18,19))*1,$A2 < = TRIM(MID(SUBSTITUTE(A$7,"-",REPT(" ",19)),2*19-18,19))*1),100-$A2,"")copied down till needed.
Formula in B8 is <trim(mid(substitute(b$7,"-",rept(" ",19)),1*19-18,19))*1,"",if(and(isnumber(a8),$b2-sum($a8:a8)="">=IFERROR(IF($C2 < TRIM(MID(SUBSTITUTE(B$7,"-",REPT(" ",19)),1*19-18,19))*1,"",IF(AND(ISNUMBER(A8),$B2-SUM($A8:A8) > 100),100,IF(AND(ISNUMBER(A8),$B2-SUM($A8:A8),100),$B2-SUM($A8:A8),IF(AND($A2 > TRIM(MID(SUBSTITUTE(B$7,"-",REPT(" ",19)),1*19-18,19))*1,$A2 < TRIM(MID(SUBSTITUTE(B$7,"-",REPT(" ",19)),2*19-18,19))*1),TRIM(MID(SUBSTITUTE(B$7,"-",REPT(" ",19)),2*19-18,19))*1-$A2,"")))),"") Copied right and down till needed.</trim(mid(substitute(b$7,"-",rept(">