[AccessD] More Of An Excel Question Really

rockysmolin bchacc.com rockysmolin at bchacc.com
Tue May 12 12:31:29 CDT 2020


Actually I don't get one because I missed that it was for Excel and not Access. To a man with Access, everything looks like...Access.

And in the  "simple is best" vein, I think Case might be better than a series of Ifs from the maintenance point of view. 

r

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of James Button via AccessD
Sent: Tuesday, May 12, 2020 10:26 AM
To: 'Access Developers discussion and problem solving'
Cc: James Button
Subject: Re: [AccessD] More Of An Excel Question Really

Yea - lets hear one for Rocky!

But - simplistic - 

For 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  (for better understanding working down the tiers)
Basic process,  starting with amounts greater than the upper range of the tier structure

=MAX(0,MIN(MAX(0,amount))-tier5)  
=MAX(0,MIN(MAX(0,amount),tier5)-tier4) 
=MAX(0,MIN(MAX(0,amount),tier4)-tier3) 
=MAX(0,MIN(MAX(0,amount),tier3)-tier2) 
=MAX(0,MIN(MAX(0,amount),tier2)-tier1)
=MAX(0,MIN(MAX(0,amount),tier1)-0)

Note the outer MAX(0,...)  to ensure the amount is positive - as in actually within the overall range of the tier structure 
The missing cap of a tier range on the first  and the lack of any modifying amount in other tiers  in the last test


That is the sort of calculation for discounts or commissions - or more usually TAX where the rate gets applied to the amounts within each rate range.
In which case the general process is to add the amounts from each tier together.
But being careful of rounding and parts of cents when showing the cash answer

For excel  name the cells  

JimB


-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of rockysmolin bchacc.com
Sent: Tuesday, May 12, 2020 5:47 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] More Of An Excel Question Really

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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list