Finding quantity that falls within a range or Tier

M

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!
 
R

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!
Click to expand...

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
 
Top