[AccessD] More Of An Excel Question Really

Tue May 12 11:47:24 CDT 2020

Two approaches - the clever approach or the easy approach.  

I always went for easy, in this case a series of If statements to see if the amount in question (N) fits into that range.  If N is greater than the top of the range, save the top of the range in an array, subtract the top of the range from N go on to the next If.  I N is less than the top, put M- bottom of range +1 into the array and Bob's your uncle.

Clever approach - an iterative function.  Satisfying but no one will EVER know how clever you've been. Until someone takes over your code and rips you a new one for leaving it undocumented and they can't figure out WFT you're doing there.

r
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren - Active Billing
Sent: Monday, May 11, 2020 5:57 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] More Of An Excel Question Really

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