0

I need to figure out how I can fill a column of twelve know percentages (each month) based on the usage of one month. We have a certain distribution key (january is 19% of the total, february is 14%, july is 1%) which over 12 months adds up to 100%. So if we know the value of one month, we can calculate the usage of a whole year. If the value of juli is 10, we know the value of january is going to be 190, of february 140, and the whole year 1000.

I can not figure out how to make excel take the value of one column, and than fill that out over the twelve months. It should look like this:

Example row (months are in Dutch btw)

Anyone have any suggestions? Thanks in advance, really stuck on this one.

Improve this question
5
  • 2
    Are you saying you know the ratio of data between months? Just apply that ratio to each column, and multiply by the total.
    – DrMoishe Pippik
    Commented Jun 8, 2022 at 13:39
  • @DrMoishePippik I am sorry, I don't quite understand. I know what percentage each month is. That doesn't change. I need to find a way to get the value of one cell, in which they tell me what month the usage is, and multiply that across each month with the corresponding percentage. So for example, they tell me the usage of april is 10. I know that april has an 8% usage of the total. I need excel then to take that usage, understand it is 8%, and fill in the missing months with the corresponding usage (january is 19%, so it should fill in 23.75 (10=8%, so 19%=23.75). Hope this makes sense.
    – Karim
    Commented Jun 8, 2022 at 15:08
  • To do this in place as you show in your picture where the user can fill any cell in that column and the rest will fill based on the percentages will take vba in a WorkSheet_Change event. It cannot be done with formula as the cell can either have a user defined value or a formula and not both.
    – Scott Craner
    Commented Jun 8, 2022 at 15:29
  • You appear to have created two accounts. In order to merge your accounts please go to superuser.com/contact and select the "I need to merge accounts" option.
    – Mokubai ♦
    Commented Jun 8, 2022 at 22:43
  • Are you randomly entering values in the third column?
    – Lee
    Commented Jun 15, 2022 at 9:06

1 Answer 1

Reset to default
0

Are you allowed to utilize other cells and keep column C reserved for formulas? If so, then here's one solution:

Cell E2: This is your input data (month).

Cell F2: This is your input data (value).

Cell G2: This is a vlookup against range A2:B13 to get the distribution value for your input month.

=VLOOKUP(E2,$A$2:$B$13,2,FALSE)

Cell H2: This is a calculation for determining your total value based on your input data.

=F2/G2

Cell C2: Multiply your distribution by the total.

=B2*$H$2

Drag down the C2 formula, and you're done.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .