Prevent over-writing results of previous calculations

[drluke]

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.

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)

 

[Crystalyzer]

If you change the column headers to actual month end dates and format them as "mmm" so they appear the way you want them, the below will work.

Book15
	A	B	C	D	E	F	G
1	Cost	Remaining Charges	Jan	Feb	Mar	Apr	Total
2	10.5	3	10.5	10.5	10.5	0	31.5
Sheet1

Cell Formulas
Range	Formula
C2:F2	C2	=IF(AND($B2>0,$B2>=MONTH(C$1)),$A2,0)
G2	G2	=SUM(C2:F2)

 

[drluke]

If you change the column headers to actual month end dates and format them as "mmm" so they appear the way you want them, the below will work.

Book15
	A	B	C	D	E	F	G
1	Cost	Remaining Charges	Jan	Feb	Mar	Apr	Total
2	10.5	3	10.5	10.5	10.5	0	31.5
Sheet1

Cell Formulas
Range	Formula
C2:F2	C2	=IF(AND($B2>0,$B2>=MONTH(C$1)),$A2,0)
G2	G2	=SUM(C2:F2)

Thanks for this.

 

[Crystalyzer]

Anytime!