Threshold Formulas - i need a formula to use across 4 thresholds

[vba_newbie83]

Hi. I am currently stuck at the moment to work out a formula to return me back the right levels

I have asset levels which I need to return back the asset number.

Below is my table


Fee Levels
$0 - $100m 3
100m+ to 1bn 4
1bn+ to 10bn 6
10bn + 8

If my asset level is $1m, then in column B I expect to return $1m, column C/D/E will be 0

With this formula if the asset level is $120m, then I expect the formula will return $100,000 column B, $20m column C & C & E is 0

Basically im trying to look for the right formula to use in cell B2, C2, D2 & E2

file attached - this is so challenging so hope someone can see what I am trying to do

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[vba_newbie83]

Hi Algiw thank you bit i am not sure what I need do do?

[JohnTopley]

In C3

=MIN($B3,10^8)

in D3

=MIN($B3,10^9)-SUM($C9:C9)

in E3

=MIN($B3,10^10)-SUM($C9:D9)

in F3

=$B3-SUM($C9:E9)