[AccessD] More Of An Excel Question Really

[Bill Benson]

What does this mean: " The formula instantly assigns those 3 x ANSWER
NUMBERS above to the relevant tiers ". Is this a single formula that you
think you can put in each Tier? Not possible. Somehow each Tier's formula
... which is essentially answering how much of that Tier is used up? Enough
to qualify for the maximum, or some lesser amount? How much of the Gross
amount that is going to sit in any Tier is =
          Max(0,Min(Gross - Sum (preceding Tier Maxes), CurrentTierMax)


On Mon, May 11, 2020 at 8:57 PM Darren - Active Billing <
darren at activebilling.com.au> wrote:

> Hi Team,
>
>
>
> I need a formula for working out dollar tiers from a given dollar amount
>
>
>
> EG assume I start with an amount of $480,000 – This amount to be divided
> into tiers.
>
>
>
> Assume tier 1 is Zero to $50,000
>
> Assume tier 2 is $50,001 to $200,000
>
> Assume tier 3 is $200,001 to $500,000
>
> Assume tier 4 is $500,001 to $1,000,000
>
> Assume tier 5 is $1,000,001 to $3,000,000
>
> And on
>
>
>
> So, from the Gross amount of 480K – what amounts (up to 480K) fit into
> what tiers?
>
>
>
> Answer:
>
> 50K of that 480 fits into tier 1 (430k remaining)
>
> 150 of that remaining 430k fits into tier 2 (280k remaining)
>
> 280 of that 280k fits into tier 3 (all monies exhausted)
>
> No further tier assignment required
>
>
>
> ANSWER NUMBERS
>
>   50+
>
> 150+
> 280 =
> --------
>
> 480
>
>
>
> How would the Answer above be built as a formula in excel though?
>
> i.e. I type in an expected gross amount into a cell (In this case 480,000)
>
> The formula instantly assigns those 3 x ANSWER NUMBERS above to the
> relevant tiers?
>
>
>
> Hope it makes sense
>
>
>
> Many thanks in advance
>
>
>
> Darren
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>