How to dynamically populate columns with the delta values

[jpravin]

I have a worksheet with rows containing the following columns

[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]Start Value[/TD]
[TD]Duration[/TD]
[TD]End Value[/TD]
[/TR]
[TR]
[TD]1435[/TD]
[TD]112[/TD]
[TD]1547[/TD]
[/TR]
[TR]
[TD]702[/TD]
[TD]431[/TD]
[TD]1133[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 65"]
<tbody>[TR]
[TD]0-100[/TD]
[TD]101-200[/TD]
[TD]201-300[/TD]
[TD]301-400[/TD]
[TD]401-500[/TD]
[TD]501-600[/TD]
[TD]601-700[/TD]
[TD]701-800[/TD]
[TD]801-900[/TD]
[TD]901-1000[/TD]
[TD]1001-1100[/TD]
[TD]1101-1200[/TD]
[TD]1201-1300[/TD]
[TD]1301-1400[/TD]
[TD]1401-1500[/TD]
[TD]1501-1600[/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]65[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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(">