I have a schedule of charges, with the number of charges being determined by a value. The total value of the charge repetitions is also shown (Total col).
In the example below, the formula that I have used to calculate the charge for every month works fine for Jan - Mar because the number of remaining charges is greater than 0. However from April onwards the number of remaining charges is not greater than 0 and my formula calculates the charge per month accordingly.
How can I change my formula (or use a different one) that will ensure that for April - Dec calculations the formula results for Jan-Mar are not recalculated to something else.
Hard copying over the results for the previous months is not possible, as I have 100's of rows in my schedule with differing number of remaining charges for each.
Any advice would be much appreciated.
In the example below, the formula that I have used to calculate the charge for every month works fine for Jan - Mar because the number of remaining charges is greater than 0. However from April onwards the number of remaining charges is not greater than 0 and my formula calculates the charge per month accordingly.
How can I change my formula (or use a different one) that will ensure that for April - Dec calculations the formula results for Jan-Mar are not recalculated to something else.
Hard copying over the results for the previous months is not possible, as I have 100's of rows in my schedule with differing number of remaining charges for each.
Any advice would be much appreciated.
Macro FAR2 (version 1).xlsb.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Cost | Remaining Charges | Jan | Feb | Mar | Apr | Total | ||||
2 | 10.50 | 3 | 10.50 | 10.50 | |||||||
3 | |||||||||||
4 | |||||||||||
5 | Cost | Remaining Charges | Jan | Feb | Mar | Apr | Total | ||||
6 | 10.50 | 0 | - | - | - | - | - | ||||
7 | 10.50 | 0 | 10.50 | 10.50 | 10.50 | - | 31.50 | (Correct result) | |||
8 | |||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =IF($B$2>0,$A$2,0) |
G2,G6:G7 | G2 | =SUM(C2:F2) |
C6:E6,F6:F7 | C6 | =IF($B$6>0,$A$6,0) |