Formula to increase % of units based on site type

Topic

New Reply

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

Reply | Quote

Replies

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
.

Reply | Quote

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.

Reply | Quote

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
.

Reply | Quote

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

Reply | Quote

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
.

Reply | Quote

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

Reply | Quote

Meleia,

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

HTH,
Maud

41783-Meleia

Place in a Standard Module:

Code:

Public Sub CostPerSite()
[COLOR=”#008000″]’—————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#008000”]’—————————-
‘CALCULATE MARKUP PERCENT AND MARKUP VALUES[/COLOR]
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
[COLOR=”#008000”]’—————————-
‘SUM MARKUP VALUES[/COLOR]
For I = LBound(t) To UBound(t)
    BasePercent = BasePercent + t(I)
Next I
End With
[COLOR=”#008000″]’—————————-
‘CALCULATE PERCENTAGE OF UNITS PER SITE AND
‘PERCENTAGE OF COST PER SITE[/COLOR]
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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

41843-Meleia3

Reply | Quote

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

Reply | Quote

Absolutely! Can I assume that the rows will always be grouped by site business in column A but not necessarily sorted?

I will need some time to make the adjustments.

Maud

Reply | Quote

That is correct. The totals that I receive are always based on the Column A business, and no they wouldn’t always be sorted.
Thanks, MM

Reply | Quote

Meleia,

Any reason why some businesses, site types, and site names/numbers are blank? Would they normally be filled?

Maud

Reply | Quote

My apologies, yes, they should be filled in. Posted the sheet before I finished all substitute data. Here is completed sheet.
Thanks
MM

Reply | Quote

Hi Meleia

..I’m sure Maud will have a nice VBA solution.

But, looking at your actual data, I think the simplest method is perhaps your original use of ‘helper’ columns.
So, just in case, here’s your v3 file updated with helper columns.
(I didn’t need the column A missing site names etc)

I noticed that your final 2 columns for ‘Total Cost per Site’ and ‘Est Yearly’ did not include the results for Business D and Business E, so I’ve included these in the final totals in my posted file.

In my version, I used a single helper column [W] that uses a VLOOKUP for the required weighting factor.
You can define/update these weighting % values in the named range [block1]
NOTE: If the Site Type in column [E] is missing or not defined (e.g. as in row 137 of your example file), then the ‘weighted Site Units’ are just the same as the number of Units for that Site.

Finally, if you re-save my posted .xlsx file as a binary .xlsb file, you will see a significant reduction in file size to approx. 410KB.
(Unfortunately, we cannot post Excel .xlsb files directly in this forum!)

zeddy
•Excel Contagion Controller
.

Reply | Quote

A = additional 25%; B = additional 15%; C = additional 10%; D = no additional %

Meleia,

Almost there. One question: From your file in post #19, I no longer see where you are applying the markup based on the site type.

Zeddy

I am thinking that with the complexity of the formatting of the sheet along with the addition of new businesses, site businesses, and markups, VBA would negate the need for all the formula and range adjustments. With everything adjusted by code automatically, I see it as the most simplest method to meet Meleia’s requirements.

Maud

Reply | Quote

Hi Maud

It will be very interesting to see a vba solution.

zeddy
•Excel Chef de Party
.

Reply | Quote

Hi Maud,

The markup helper column with formula used appears in the first uploaded version to this post. This will give you the clearest picture of how the markup is used.
Thanks
MM

Reply | Quote

Hi Zeddy,
Thanks for the solution above. Have been in the middle of another project but will be looking at this one again soon. I appreciate all of your assistance.

Maud, I’m still interested in a VBA solution that would negate formula and range adjustments if you still have the time. I planned ahead this year; the sheet isn’t needed until the beginning of October!

Again, thanks everyone.
MM

Reply | Quote

Hi Maud,

Hoping that a full-on VBA solution is still on your radar! Just checking before I begin to set up a formula/range sheet.

Thanks
MM

Reply | Quote

Meleia,

In the middle of a “go-live”. Still on my radar.

Maud

Reply | Quote

Meleia,

The attached worksheet should do what you want. For the sake of clarity, for a particular company (col A) let’s call the section range of Business A, Business B, Business C, etc., a business block

Changing a site type affects all the business blocks for that business. Therefore, if you change a new site type for a business, all the Businesses A through Other for that business will update across automatically.

Select any cell in a business block and the block highlights. If you change one of the block values, only the highlighted business block will be automatically updated.

You can add/change additional Markup Values by clicking on the Markup Button. A form will open where you are prompted to make changes. You can have up to 10 Markups. The data validation dropdowns will be updated to reflect the changes

You can add/delete additional business rows to existing businesses or create new ones at the bottom. The only rule is that the last of the last line of the business is the Totals row with the cell in col A having the value of: name of the business Total (ex. Checkers Total). The sum formulas will be created/adjusted by the code

You can add/delete additional Business blocks across (Business G, Business H, etc.) as long as the last 3 columns are: a blank column, Total Cost Per Site, and Est Yearly).

Note that my values will be different from the values in your sample because the code is applying the markups where yours are not. I have noted that you have hidden columns. Don’t delete any of them as the code was built with them in place. You could of course hide or expose them. Let me know if you will be changing any of the sheet names as I have to make adjustments in the code.

I am sure we will have to make some tweaks but they will be minor. The reset button will reset the code if needed.

HTH,
Maud

Reply | Quote

Maud,

All I can say is WOW! Can’t believe the work you put into this. Believe it will be exactly what I need. I’ll be starting to load data into the sheet in the next week and will let you know if there are any tweaks. But it looks perfect! I can’t thank you enough. And again, the level of work and detail is fantastic.

The results from you and Zeddy will both be put to use; Zeddy’s range/formula solution on another project.
All of your efforts are much appreciated.

MM

Reply | Quote

Hi Maud,

Have run into a couple of problems. When some of the % of Cost per Site totals are changed, the values of that site don’t change. For example (using sheet above), if I change the value in Y43 (the total row), the values in Y3:Y42 should change, and Y3:Y42 should equal the new number typed into the Total row, Y43. This does not happen in all of the instances.

Also, if I make changes in the % of Cost per Site in Business B, the values in Business D change — this is correct. However the Total in Business D doesn’t change to reflect the new Total.
And, If Units per Site are changed in Business E, the corresponding change does not happen in Business D as it should. I can change these values to reflect Business D = Business E, if it doesn’t interfere with anything else.

And lastly, just a question. When I highlight a total, for example Y43 above, the data for the NEXT site highlights. What is the purpose of this?

Any help appreciated, as always.
Meleia

Reply | Quote

I am sure we will have to make some tweaks but they will be minor.

I’ll take a look at it this weekend.

Maud

Reply | Quote

Hi Maud,

Anything new on this?

Thanks
Meleia

Reply | Quote

Meleia,

Got a little backed up. Thanks for the reminder.

Maud

Reply | Quote

Hi Maud,

Are you still looking at this? If not, no problem; will make what I have work.

Thanks
Meleia

Reply | Quote