Prevent over-writing results of previous calculations

drluke

Active Member
Joined
Apr 17, 2014
Messages
312
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1CostRemaining ChargesJanFebMarAprTotal
210.50310.5010.50
3
4
5CostRemaining ChargesJanFebMarAprTotal
610.500-----
710.50010.5010.5010.50-31.50(Correct result)
8
Sheet2
Cell Formulas
RangeFormula
C2C2=IF($B$2>0,$A$2,0)
G2,G6:G7G2=SUM(C2:F2)
C6:E6,F6:F7C6=IF($B$6>0,$A$6,0)
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
ABCDEFG
1CostRemaining ChargesJanFebMarAprTotal
210.5310.510.510.5031.5
Sheet1
Cell Formulas
RangeFormula
C2:F2C2=IF(AND($B2>0,$B2>=MONTH(C$1)),$A2,0)
G2G2=SUM(C2:F2)
 
Upvote 0
Solution
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
ABCDEFG
1CostRemaining ChargesJanFebMarAprTotal
210.5310.510.510.5031.5
Sheet1
Cell Formulas
RangeFormula
C2:F2C2=IF(AND($B2>0,$B2>=MONTH(C$1)),$A2,0)
G2G2=SUM(C2:F2)
Thanks for this.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top