# [AccessD] More Of An Excel Question Really

rockysmolin bchacc.com rockysmolin at bchacc.com
Tue May 12 13:16:37 CDT 2020

```" How would the Answer above be built as a formula in excel though?"

Excel supports VBA.  You can do everything behind the spreadsheet with code.  Makes some things much easier.

r

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of gordons2 at pacbell.net
Sent: Tuesday, May 12, 2020 11:13 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] More Of An Excel Question Really

Here is a simple one I use in my estimating system to calculate bonds.

-----Original Message-----
From: AccessD <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 <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?

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

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

Darren

--
AccessD mailing list