ExcelBanter - Spread values across fields

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Spread values across fields (https://www.excelbanter.com/excel-worksheet-functions/445954-spread-values-across-fields.html)

Spread values across fields

Hi all,

I am struggling with a formula. I would like to read a value, divide it by 12 and then write this value to 12 different fields. I have developed the formula below to achieve this, which takes the value 1000 from field A1, and splits its across fields A2-A13.

=ROUNDDOWN($A$1/12,2)

This all works fine, but I would like to take things a bit further. The formula above writes 83.33 into fields A2-A13, which is correct. If we say 83.33 is 100% of the value, what I then want to do, is have fields A6 and A10 show only 70% of the value. Therefore both of these fields would need to show approx 58.331. I assume the formula below is the best way to achieve this?

=ROUNDDOWN($A$8/12,2)*0.7

The next stop of the formula is causing me the biggest problem, the remaining fields need to take up the slack and ensure the total value of fields A2-A13 equals the original value of 1000. Obviously, we now know fields A6 and A10 total 116.662, therefore meaning we have 883.338 remaining. I want this value to be evenly spread across the other 10 fields, therefore the value of 88.33 should be written to these fields. Does anyone have a suggestion on how I would achieve this?

Many thanks

Quote:

Originally Posted by sjc07 (Post 1601436)

Hi all,
I am struggling with a formula. I would like to read a value, divide it by 12 and then write this value to 12 different fields. I have developed the formula below to achieve this, which takes the value 1000 from field A1, and splits its across fields A2-A13.
=ROUNDDOWN($A$1/12,2)
This all works fine, but I would like to take things a bit further. The formula above writes 83.33 into fields A2-A13, which is correct. If we say 83.33 is 100% of the value, what I then want to do, is have fields A6 and A10 show only 70% of the value. Therefore both of these fields would need to show approx 58.331. I assume the formula below is the best way to achieve this?
=ROUNDDOWN($A$8/12,2)*0.7
The next stop of the formula is causing me the biggest problem, the remaining fields need to take up the slack and ensure the total value of fields A2-A13 equals the original value of 1000. Obviously, we now know fields A6 and A10 total 116.662, therefore meaning we have 883.338 remaining. I want this value to be evenly spread across the other 10 fields, therefore the value of 88.33 should be written to these fields. Does anyone have a suggestion on how I would achieve this?
Many thanks

<<<<< HELP from BRAZIL

Dear sjc07, Good Afternoon.

I did an example for you.

Take a look at it and tell me if it worked for you.

Fell free to ask anything about your question.

Have a nice day.

Spread values across fields

I have developed
the formula below ... which takes the value 1000 from field
A1, and splits its across fields A2-A13.

=ROUNDDOWN($A$1/12,2)

I would like to take things a bit further. The
formula above writes 83.33 into fields A2-A13, which is correct. If we
say 83.33 is 100% of the value, what I then want to do, is have fields
A6 and A10 show only 70% of the value. Therefore both of these fields
would need to show approx 58.331. I assume the formula below is the best
way to achieve this?

=ROUNDDOWN($A$8/12,2)*0.7

The ...
remaining fields need to take up the slack and ensure the total value of
fields A2-A13 equals the original value of 1000.
fields, therefore the value of 88.33 should be written to these fields.

If I understand the requirement, one way is to put
=ROUNDDOWN($A$1/12,2)*0.7
in both A6 and A10, and then put this in the remaining 10 places:
=($A$1-$A$6-$A$10)/10

Round the results as needed.

Hope this helps getting started.