PDA

View Full Version : Solved: Formula to allocate absolute values

danesrood
07-21-2010, 01:00 PM
Dear All

I have a need to allocate a value which will change from month to month against the percentage loadings for each site. The allocated value can only be a whole number and the total amount of the allocated values must total the original value. In a perfect world I would prefer to use the 2 decimal places shown below but if it helps I could round up or down. All I am trying to achieve is a reasonably fair and accurate method of allocating the costs.

Value to be allocated say 34

Exeter 21.60%
London 16.99%
Glasgow 19.54%
Edinburgh 5.39%
Birmingham 21.10%
Bath 15.38%
Totals 100.00%

The values will change every month and the percentages annually.

Many thanks

mdmackillop
07-21-2010, 01:10 PM
I don't know if you are talking about Spreadsheet or VBA calculation

If the former, simply use the names as constant variables eg
Const Exeter = 0.216
Const London = 0.1699

If in a spreadsheet, you can use range names for each town and set these equal to the value. Then =A1*Exeter etc.

danesrood
07-21-2010, 01:23 PM
I was looking for a formula that would produce a reasonably accurate and fair whole number value for each building based on the percentage loading that must add up to the total to be allocated.

mdmackillop
07-21-2010, 01:25 PM
Can you post a sample workbook? I don't understand what is required.

danesrood
07-21-2010, 02:01 PM
Certainly.. file attached which I hope explains my need.

I have to say that the number to be allocated is usually less than 100 each month.

mdmackillop
07-21-2010, 02:38 PM
Select D4: D8 and enter =ROUND(\$C4:\$C8*D3,0) as an array formula (Control/Shift/Enter).
In D9 enter =D3-SUM(D4: D8)
Copy to the right.

Aussiebear
07-21-2010, 03:27 PM
MD, Is it because of the "rounding" that you require the different formula in cell D9?

RonMcK
07-21-2010, 07:38 PM
Spot on, he's forcing the last item to be whatever it needs to be so the column totals the original fixed amount. This prevents the total of the allocated amounts from occasionally being off by +1 or -1.

Cheers!

mdmackillop
07-22-2010, 12:04 AM
As Ron says. You may wish to make Exeter or Birmingham the "odd one" as the correction value will be less significant in a higher percentage. ie 10 v
9 as opposed to 4 v 3

danesrood
07-22-2010, 10:35 AM
mdmackillop

Thank you that will do fine.

As you say I can juggle around which one to use as the catch all.

My sincere thanks for taking the time

Regards

danesrood