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:
<tbody>
</tbody>
Needs to be converted tot his table:
<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):
<tbody>
</tbody>Becomes:
<tbody>
</tbody>
C) Until now i only covered a single row, however not every row starts at the same column. Example:
<tbody>
</tbody>Becomes:
<tbody>
</tbody>
And not this:
<tbody>
</tbody>
I think part (C) could be done first, by creating an intermediary table by offsetting the values first before doing any calculation:
<tbody>
</tbody>
Thanks!
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>
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>
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!
Last edited: