Finding quantity that falls within a range or Tier
 

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!

--
Thank you

Finding quantity that falls within a range or Tier
 

On Fri, 11 Jul 2008 16:39:01 -0700, Mimi
wrote:

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

Finding quantity that falls within a range or Tier
 

It worked perfectly, thank you so much Ron!

Mimi
--



"Ron Rosenfeld" wrote:

On Fri, 11 Jul 2008 16:39:01 -0700, Mimi
wrote:

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

Finding quantity that falls within a range or Tier
 

On Sat, 12 Jul 2008 02:40:00 -0700, Mimi
wrote:

It worked perfectly, thank you so much Ron!

Mimi

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