Finding quantity that falls within a range or Tier

[Mimi]

Cell A1 will be used to enter the number of sales. Then I need to know, from
the number of sales entered how many of them fall within each one of the
Tiers listed below. For example if we have 140 sales then the first 114 fall
within tier A, 20 fall within tier B and 6 fall within Tier C.

Tier A (0 - 114)
Tier B (115 - 134)
Tier C (135 - 159)
Tier D (160 - 189)
Tier E (190 - 219)
Tier F (220 - 249)
Tier G (250 - 299)
Tier H (300 - 399)
Tier I (400 +)

I have been trying to create a multiple if formula but can't get even close
to creating the right one. Please help!

 

[Ron Rosenfeld]

Cell A1 will be used to enter the number of sales. Then I need to know, from
the number of sales entered how many of them fall within each one of the
Tiers listed below. For example if we have 140 sales then the first 114 fall
within tier A, 20 fall within tier B and 6 fall within Tier C.

Tier A (0 - 114)
Tier B (115 - 134)
Tier C (135 - 159)
Tier D (160 - 189)
Tier E (190 - 219)
Tier F (220 - 249)
Tier G (250 - 299)
Tier H (300 - 399)
Tier I (400 +)

I have been trying to create a multiple if formula but can't get even close
to creating the right one. Please help!

Here's one way:

Put the tier upper limits in cells B1:I1

B1: 114
C1: 134
D1: 159
etc.

In J1 put a very large number

J1: 1E+100


Enter formulas:

B2: =MIN(A1,B1)
C2: =MIN($A$1-SUM($B$2:B2),C$1-SUM($B$2:B2))

Then fill right to J2

--ron

 

[Mimi]

It worked perfectly, thank you so much Ron!

Mimi

 

[Ron Rosenfeld]

It worked perfectly, thank you so much Ron!

Mimi

Glad to help. Thanks for the feedback.
--ron