A formula for weighting please...

[skinnea]

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

 

[Gerald Higgins]

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]

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]

Sorry for any confusion - it's obviously clear in my head , 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...


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]

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.