Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you spread Pivot Table "Value" Fields in seperate columns | Excel Discussion (Misc queries) | |||
Combining spread sheets with common fields | Excel Discussion (Misc queries) | |||
spread out fields in different column | Excel Programming | |||
is it possible to select and change the values of cells in a minimized excel spread sheet from vba? | Excel Worksheet Functions | |||
Macro to spread values between start and end date | Excel Programming |