A formula for weighting please...

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126
I have a set of NUMBERS, and each NUMBER in that set can be any positive integer.

I want to split each NUMBER across 3 TYPES (A, B, and C).
There can be any QUANTITY of each of the 3 TYPES (from 0 to positive integer n)
Sometimes the QUANTITY of TYPE C and/or TYPE B is greater than the QUANTITY of TYPE A

Regardless of the total QUANTITY of TYPES A, B, and C in total, is there a formula that will split each NUMBER across the 3 TYPES in such a way that;
• Each QUANTITY of each TYPE has the same ‘share’ of the overall NUMBER
• 1 QUANTITY of TYPE A is always greater than 1 QUANTITY of TYPE B, which in turn is always greater than 1 QUANTITY of TYPE C

For example;

there are 3 QUANTITIES of TYPE A, all equal to 100
there are 2 QUANTITIES of TYPE B, all equal to 75
there are 5 QUANTITIES of TYPE C, all equal to 50

Where the NUMBER as a whole was 700.


It's driving me and my team up the wall, we just can't figure this one out :(
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
This....
Sometimes the QUANTITY of TYPE C and/or TYPE B is greater than the QUANTITY of TYPE A

.... seems to contradict this ....
• 1 QUANTITY of TYPE A is always greater than 1 QUANTITY of TYPE B, which in turn is always greater than 1 QUANTITY of TYPE C

Can you give us a couple of different worked examples please ?
 

mrMozambique

Board Regular
Joined
Mar 9, 2005
Messages
97
Confusing indeed! :^)

Can you give us a sample data set in Excel and where you want the formulae? I'm a little uncertain about what the formula is supposed to do here. No promises, but it will help us understand better your situation in any case.
 

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126
Sorry for any confusion - it's obviously clear in my head :biggrin:, but I see how it might not have come out right...

I was trying to say that the size of each TYPE A (if there are any) needs to be bigger than the size of each TYPE B (if there are any), which in turn needs to be bigger than the size of each TYPE C (if there are any).

The complication arises because in any individual case there may (or may not) be any TYPE As, or TYPE Bs, or TYPE Cs.

Let me try and show you some examples of these scenarios.
NB - I don't have answers for them that's why I need a formula... :oops:


Example 1

NUMBER = 1000
I have 2x TYPE A
I have 1x TYPE B
I have 4x TYPE C

I need 1000 to be divided into 7 parts (2+1+4), I need both the A's to be the same size as each other, and all four C's to be the same size as each other. But I need each one of the 2x TYPE As to be greater in size than each one of the 4x TYPE Cs.

========
Example 2

NUMBER = 2000
I have 0x TYPE A
I have 4x TYPE B
I have 6x TYPE C

I need 2000 to be divided into 6 parts (0+4+6), I need all four B's to be the same size as each other, and all six C's to be the same size as each other. But I need each one of the 4x TYPE Bs to be greater in size than each one of the 6x TYPE Cs.


========
Example 3

NUMBER = 12,000
I have 15x TYPE A
I have 1x TYPE B
I have 1x TYPE C

I need 12,000 to be divided into 17 parts (16+1+1), I need all fifteen A's to be the same size as each other, and I need each one of the 15x TYPE As to be greater in size than the single TYPE B, which itself needs to be greater in size than the single TYPE C.



In all the real life cases, I'll know the NUMBER, and how many of each TYPE I have. What I want to work out is what each A, B or C is equal to; and I need it to be a repeatable forumula so that it always works out on the same criteria.

Does that make it any clearer...?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
There is not necessarily any solution if all the values have to be non-negative integers.

If they need not be integers, then there are an infinite number of solutions.

For example, for your first problem nA=2, nB=1, NC=4. Let B=C+1 and A=B+1. Then

C =(Total - nB - 2*nA) / (nA+nB+BC) ~ 142.14, so B ~ 143,14 and A ~ 144.14

To find integer solutions, I'd use Solver, e.g.,

C=140, B=144, A=148.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top