converting a table using a set of rules

[Dendro]

Hi,
I need some help with converting a table to another table depending on a few rules, using VBA. I want to see the daily increase in the new table.

I will try to post some VBA code soon ( i changed some code as it was not working and at now i can't even explain why i wrote some lines, it wouldn't help this thread at this point).

Rules:
A) Every cell (Y) in the new table needs to become the subtraction of the corresponding cell in the old table (X) minus the previous cell (column) in the old table(X-1) (and divided by the offset between the two old cells-> see part B; in this case it’s all divided by 1). Let’s use the following row as an example to show what i need:

4

5

8

9

10

10

<tbody>
</tbody>

Needs to be converted tot his table:

nothing here as there is no value left of 4 to calculate an increase

(5-4)/1=1

(8-5)/1=3

(9-8)/1=1

(10-9)/1=1

(10-10)/1=0

<tbody>
</tbody>

Pretty easy so far, a simple formula would suffice...

B) When a value is missing subtract the next non-empty cell (X+...) minus the previous cell value (X-…) and divide it by the offset between both cells (create an average):

1

2

3

4

8

14

<tbody>
</tbody>

Becomes:

nothing here

(2-1)/1=1

(3-2)/2=0.5

(3-2)/2=0.5

(4-3)/1=1

(8-4)/1=4

(14-8)/3=2

(14-8)/3=2

(14-8)/3=2

<tbody>
</tbody>

C) Until now i only covered a single row, however not every row starts at the same column. Example:

1	2	3
	8	9

<tbody>
</tbody>

Becomes:

nothing here	(2-1)/1=1	(3-2)/1=1
nothing here	(9-8)/1=1

<tbody>
</tbody>

And not this:

nothing here	(2-1)/1=1	(3-2)/1=1
	nothing here	(9-8)/1=1

<tbody>
</tbody>

I think part (C) could be done first, by creating an intermediary table by offsetting the values first before doing any calculation:

1	2	3
8	9

<tbody>
</tbody>

Thanks!