# Thread: Formula to increase % of units based on site type

1. ## Formula to increase % of units based on site type

Hi All,
I need a formula to increase the % of Units per Site depending on the Site Type. The % of Units per Site is based on the number of units per site.
An example, if Site Type = A then the % of Units per Site would increase by 25%, which would reduce the %'s in the other Sites as the total would remain 100%.
Hopefully the attached file will explain the process.

Any assistance appreciated.

Thanks,
Meleia

2. Hi Meleia

Suppose I gave you a formula as you ask.
And then I change all the Site Types to A.
If you imagine that you only have A's in your list, then you cannot increase each % in column [D] by 25% and still have a total of 100%.
Similarly, if you only have A's and B's as your site types, you cannot increase their % by 25% and 15% and still have 100% for your total.

We have people here who are very good at hard sums.

zeddy
•Excel Sideband Clutter Clearer
.

3. Originally Posted by zeddy
Hi Meleia

Suppose I gave you a formula as you ask.
And then I change all the Site Types to A.
If you imagine that you only have A's in your list, then you cannot increase each % in column [D] by 25% and still have a total of 100%.
Similarly, if you only have A's and B's as your site types, you cannot increase their % by 25% and 15% and still have 100% for your total.

We have people here who are very good at hard sums.

zeddy
•Excel Sideband Clutter Clearer
.

Knew I wasn't going to explain this very clearly. See attached file v2. Since I have only a total Cost that I need to divide across all the Sites, I am using a % of the number of Units. However, the Site Type is going to make those Units worth more or less when it comes to total cost, and that is based on the % of Units per Site.

Attached v2 shows what I would need using helper columns. I can do it this way, but wondered if there was a cleaner way to get the same result.

4. Hi Meleia

OK, it makes more sense now. Mostly.
..Let me think of an elegant formula, and I'll post it back tomorrow.

zeddy
.

5. Originally Posted by zeddy
Hi Meleia

OK, it makes more sense now. Mostly.
..Let me think of an elegant formula, and I'll post it back tomorrow.

zeddy
.
Hi Zeddy,
This might help with the "mostly" part: what I am determining is business interruption valuation. Each of the sites has a specific number of subscriber "units". For the cost (actually revenue in real data) I receive only one total number that needs to be spread across all the sites. To apportion the revenue I use the percentage of the subscriber units. Now, some of the sites are worth more than others, so I can't base the revenue entirely on the percentage of subscriber units. Some sites will be worth 25% more than other sites (and the 25% is arbitrary for this example). That "worth" is based on the value of each individual site's assets (the increase being grouped into A, B, or C). This gives me a more accurate business interruption value. HTH and thanks for any assistance. Perhaps your something elegant will include an easy way to change the percentages of A, B, and C for various scenarios?
MM

6. Hi Meleia

..I am looking at three methods of 'distributing' the 'cost' across the sites based on weighting % and site type.
I have a method of easily setting the scaling %age values.

zeddy
•Excel Distrubutions Distributor
.

7. Originally Posted by zeddy
Hi Meleia

..I am looking at three methods of 'distributing' the 'cost' across the sites based on weighting % and site type.
I have a method of easily setting the scaling %age values.

zeddy
•Excel Distrubutions Distributor
.
Thank you most Excellent Distrubutions Distributor

8. Meleia,

Here is a vba solution. Click the Calculate button and the Percent of Cost Per Site will be calculated.

HTH,
Maud

Meleia.png

Place in a Standard Module:
Code:
```Public Sub CostPerSite()
'----------------------------
'DECLARE AND SET VARIABLES
Dim Units As Range, Markup As Range
Dim s(), t(), u(), BasePercent As Double
With Worksheets("Sheet1")
Set Units = Range("C4:C34")
Set Markup = .Range("A4:B7")
Count = 1
'----------------------------
'CALCULATE MARKUP PERCENT AND MARKUP VALUES
For Each cell In Units
ReDim Preserve s(Count):: ReDim Preserve t(Count)
s(Count) = WorksheetFunction.Index(Markup, _
WorksheetFunction.Match(cell.Offset(0, -2), _
.Range("A4:A7"), 0), 2)
t(Count) = cell * s(Count)
Count = Count + 1
Next cell
'----------------------------
'SUM MARKUP VALUES
For I = LBound(t) To UBound(t)
BasePercent = BasePercent + t(I)
Next I
End With
'----------------------------
'CALCULATE PERCENTAGE OF UNITS PER SITE AND
'PERCENTAGE OF COST PER SITE
Count = 1
For Each cell In Units
ReDim Preserve u(Count)
u(Count) = t(Count) / BasePercent
Cells(Count + 3, 4) = u(Count) * Range("D35")
Count = Count + 1
Next cell
End Sub```

9. ## The Following User Says Thank You to Maudibe For This Useful Post:

Myers515 (2015-08-20)

10. Thanks for the VBA solution Maud.
Since I don't know VBA, can you describe how the percent and markup is being calculated? And if I need to change the %'s, say A's will be worth 30% more instead of 25% more, how would that change be accomplished? Also, if a site type other than A, B, C, or D is entered into the sheet it throws an error; is there a simple error handing function to pop up and say "No, wrong site type"?
Thanks for your assist. Always appreciated.
MM

11. Hi Meleia

This attached file uses just formulas and named cells. The numbers are the same as Maud's vba results.

Essentially, we can total all the units for each of the site types.
We can then scale each 'site type total' up according to the specified weighting factor for each 'site type'. This will then allow us to split the Distribution Total to each of the four site types, based on the weighted totals specified for each 'site type'.

Now that we know how much is to be allocated to site type A, we can then distribute A's total amount to each of the individual type A sites according to their own percentage of that site type's total units.

I have made provision for another site type to be defined, and each site type in column A can be selected from a dropdown.

zeddy
•Excel Centurion Jobber

(OK, that's 100 jobs I've had, so I'm starting back at the beginning again. Unless I don't)

12. ## The Following User Says Thank You to zeddy For This Useful Post:

Myers515 (2015-08-20)

13. Thanks so much Zeddy and Maud. Since I am unfamiliar with VBA, I'll be using Zeddy's solution as I need to expand this to cover several types of "costs" with several thousand sites and possibly additional site types. Maud, I still plan on going through your solution to see if I can figure out how to expand for additional sites, etc.

Can't thank you enough, not only for your Excel skills but your mathematical prowess!
MM

14. Hi Meleia,

I have adjusted the code to allow you to insert unlimited additional sites. As long as you keep you totals row at the bottom the code will adjust to accommodate the inserted rows. No need to change the code or add formulas.

HTH,
Maud

15. Thanks Maud, will try this on the expanded file.
Appreciate all of your help, always.
MM

16. Meleia,

I did not see you questions in post#9, apologies! The following revision addresses them.

1. Clicking on the Markup Button will open a form and allow you to add or remove up to 10 Site Types.
2. I added data validation so you must select a correct Site Type.
3. Data validation is dynamic in that the list will grow or shrink with the number of Site Types.
4. The table will automatically update when changes are made to columns A or C. I left the calculation button in place in case you clear the resulted % of Cost per Site values (Col D).
5. Both a Site Type and Units per Site must be populated for the update to occur (one or the other cannot be left blank)

HTH,
Maud

Meleia3.png

17. Thanks for the extra work on this Maud. And of course, that's never the end of it. I didn't initially think about a VBA solution so really pared down the data.
The attached file shows the expanded sheet as it will be used, plus it would need to be able to grow with new Site Businesses (column A) and new Businesses (Business A, Business B, etc.).
Is it possible for the VBA to accommodate the full layout and the possible changes?

I can go Zeddy's route with Ranges if needed.

Thanks
MM

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•