Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Star Lounger
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 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
    .

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post

    Smile

    Quote Originally Posted by zeddy View Post
    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
    .

    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.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 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
    .

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    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
    .
    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. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 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
    .

  7. #7
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    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
    .
    Thank you most Excellent Distrubutions Distributor

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 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
    Attached Files Attached Files
    Last edited by Maudibe; 2015-08-19 at 22:28.

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

    Myers515 (2015-08-20)

  10. #9
    Star Lounger
    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

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 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)
    Attached Files Attached Files
    Last edited by zeddy; 2015-08-20 at 14:37.

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

    Myers515 (2015-08-20)

  13. #11
    Star Lounger
    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

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 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
    Attached Files Attached Files

  15. #13
    Star Lounger
    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

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 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
    Attached Files Attached Files

  17. #15
    Star Lounger
    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
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

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