| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding whether a date falls between a certain category | Excel Worksheet Functions | |||
| check whether a date falls in a range | Excel Discussion (Misc queries) | |||
| Checking if a certain day falls in a range | Excel Discussion (Misc queries) | |||
| Data falls in a range +/-1 | Excel Worksheet Functions | |||
| how to count if the value falls between a date range | Excel Discussion (Misc queries) | |||
Hybrid Mode
