Formula to Calculate Budget over a period of years

Rashid Khan

New Member
Dear All,
I am enclosing herewith my sample file with the problem highlighted

I have years in Col G2 till end viz year 2019 above or below years
In Column H I have allocation symbols viz A or P till down
In Column I, I have Total Cost eg 1200 and different values for each cell below
In Cell J2, I have 2019, Cell K2 I have 2020, Cell L2 I have 2021, Cell M2 I have 2022
I want a formula under J to M Columns which should compare it with the years in Col G2
If the value of Year in J2 is less than the year value of year in G2 then it should show zero
otherwise it should show proportion of the Total Cost according to the years shown in Col J to Col M

Any help with formula or VBA would be appreciated

TIA
Rashid

Attachments

• 12.3 KB Views: 13

bosco_yip

Excel Ninja
Try,

In A3, copied across to Q3 and all copied down :

=IF((\$H3=J\$1)*(YEAR(J\$2)>=\$G3),IF(YEAR(J\$2)=\$G3,\$I3/12*(12-MONTH(\$F3)),\$I3),"")

Regards
Bosco

Rashid Khan

New Member
Dear Bosco
Thanks a lot for your help

Regards
Rashid