[AccessD] More Of An Excel Question Really

[Stuart McLachlan]

One way:

Cells:
A1 = "Amount"
B1 = 50000
C1 = 200000
D1 = 500000
E1 = 1000000
F1 = 3000000

B2 =  =MIN(A2,B$1)
C2 =  =MIN($A2-SUM($B2:B2),C$1-B$1)

Copy C2 to D2..F2

Copy (C2 .. F2 )
into  D2 , then E2 etc

Enter amounts into A2,A3,A4 etc


-- 
Stuart


On 12 May 2020 at 10:56, Darren - Active Billing 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