• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • sample.xlsx
    12.3 KB · Views: 14
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
 
Back
Top