[AccessD] More Of An Excel Question Really

Tue May 12 13:24:55 CDT 2020

Definitely IF's or a compound conditional statement  the latter making that the thing will be a set of if's 
 Case seems inappropriate in this case as the answer is a set of values rather than just the single selection.

And as far as Excel code goes  the primary conditioning for a code answer is what is to be done with the answer.

Excel does not easily deal with multiple answers from 1 calculation
Unless using the New all singing and dancing - OK caterwauling and confusing - array formulas in [Microsoft 365] ™©®

JimB

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

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

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