[AccessD] More Of An Excel Question Really
John Bartow
jbartow at winhaven.net
Tue May 12 12:15:22 CDT 2020
Hi Darren,
Might this be a good place to use the Case Statement?
https://www.techonthenet.com/access/functions/advanced/case.php
John B
-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Darren - Active Billing
Sent: Monday, May 11, 2020 7:57 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
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
More information about the AccessD
mailing list