• 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.

Cost calculation based on start year and frequency (Solved)

Hello Excel experts,

Happy New Year!

I need your assistance in solving a logic. Please find attached the file wherein I have two tables, top one is the input and the one below is where the results should populate (dark yellow shaded cells).

Table 1:
Column B - Cost description
Column C - Cost type to choose: Fixed or Variable
Column D - Start year for each cost. Inputs are restricted with data validation from year 1-20. If its year 2, the cost should start from year 2 onwards.
Column E - Frequency of the cost. Inputs are restricted with data validation from year 0-20. For example, if the frequency of cost 1 is 0 means, it's a one time cost that should sit in only year 1 of the table below. If the frequency of cost 2 is 3 years, the cost should incur every years starting from year 2.
Column F - Total of each cost

Table 2:
Column B - I have years from 1-20
Column C - Fixed cost wherein I need all the fixed costs of the above table added as per the start year and frequency.
Column D - Variable cost wherein I need all the variable costs of the above table added as per the start year and frequency.

Thank you for your valuable time and effort.
 

Attachments

  • Excel logic 01a.xlsx
    11.1 KB · Views: 2
Last edited by a moderator:
Hi, Happy New Year!

Here is the forum rule:


1. You needed to tell us: "what do you want to achieve?"

Please give us your expected result and criteria.

and resubmit your file stating the above mentioned.

Note: Please also take note that, since you are not a new member, we shall close this thread should you fail to take any action.

Regards
 
Last edited:
Hi,

Thanks. I am re-attaching the file with the desired results.

In short, all the fixed costs and variable costs should sum up (year wise) in the Column C & D in the table 2 based on the criteria below.

Criteria - The cost should start from the "From year?" column in table 1 and based on the frequency year in column E, total sum of the costs should be reflected in the corresponding years in the table 2.

For example, if the frequency of cost 1 is 0 means, it's a one time cost that should sit in only year 1 of the table 2. If the frequency of cost 2 is 3 years, the cost should incur every years starting from year 2 and so on for the rest of the cost items.

I hope this helps. Thank you for the time.
 

Attachments

  • Excel logic 01a.xlsx
    11.2 KB · Views: 3
In the attached a Power Query offering at table at cell F14.
It looks at the table top left and the cell at H3 (a Named range called Years) to create that table.
Alter the data in the top left table, change the number of years you want in cell H3, right-click anywhere in the results table and choose Refresh.
I've left your table at B13 for comparison only.
 

Attachments

  • Chandoo50810Excel logic 01a.xlsx
    21.9 KB · Views: 2
Back
Top