Results 1 to 15 of 33

20150818, 10:28 #1
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
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

20150818, 12:15 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,344
 Thanks
 162
 Thanked 621 Times in 590 Posts
Hi Meleia
..you need to have a rethink about this.
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.
Have another think about what you are asking for.
We have people here who are very good at hard sums.
zeddy
•Excel Sideband Clutter Clearer
.

20150818, 13:02 #3
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
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.

20150818, 15:29 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,344
 Thanks
 162
 Thanked 621 Times in 590 Posts
Hi Meleia
OK, it makes more sense now. Mostly.
..Let me think of an elegant formula, and I'll post it back tomorrow.
zeddy
•Excel Gigabertz Uploader
.

20150819, 10:18 #5
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
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

20150819, 15:07 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,344
 Thanks
 162
 Thanked 621 Times in 590 Posts
Hi Meleia
..I am looking at three methods of 'distributing' the 'cost' across the sites based on weighting % and site type.
I need to think about this a little more before I post my recommendation.
I have a method of easily setting the scaling %age values.
zeddy
•Excel Distrubutions Distributor
.

20150819, 16:45 #7
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post

20150819, 21:26 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
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
Last edited by Maudibe; 20150819 at 21:28.

The Following User Says Thank You to Maudibe For This Useful Post:
Myers515 (20150820)

20150820, 12:18 #9
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
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

20150820, 13:33 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,344
 Thanks
 162
 Thanked 621 Times in 590 Posts
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)Last edited by zeddy; 20150820 at 13:37.

The Following User Says Thank You to zeddy For This Useful Post:
Myers515 (20150820)

20150820, 17:09 #11
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
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

20150824, 22:59 #12
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
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

20150825, 07:50 #13
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
Thanks Maud, will try this on the expanded file.
Appreciate all of your help, always.
MM

20150825, 21:50 #14
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
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

20150826, 13:53 #15
 Join Date
 Nov 2008
 Location
 Northeast US
 Posts
 81
 Thanks
 10
 Thanked 1 Time in 1 Post
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