| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calculate within a range?
B5 100000
B6 150000 I'm trying to work out a ratio of the above data that fits within specific limits. I need to have B6/B5, but only if the result is greater than .8 or less than 1.2. If this ratio is less that .8, I want .8 to be the answer. If the result is greater than 1.2, I need 1.2 to be the answer. In english, my IF statement is below IF (B6/B5).8 and <1.2, then B6/B5 IF (B6/B5)<.8, then .8 IF (B6/B5)1.2, then 1.2 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate within a range?
=IF(AND(B6/B5.8,B6/B5<1.2,B6/B5,IF(B6/B5<.8,.8,IF(B6/B51.2,1.2)))
Dave -- Brevity is the soul of wit. "Jdd561" wrote: B5 100000 B6 150000 I'm trying to work out a ratio of the above data that fits within specific limits. I need to have B6/B5, but only if the result is greater than .8 or less than 1.2. If this ratio is less that .8, I want .8 to be the answer. If the result is greater than 1.2, I need 1.2 to be the answer. In english, my IF statement is below IF (B6/B5).8 and <1.2, then B6/B5 IF (B6/B5)<.8, then .8 IF (B6/B5)1.2, then 1.2 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate within a range?
You will need to account for B6/B5 = 0.8 or =1.2, or it returns FALSE.
=IF(AND(B6/B5=0.8,B6/B5<=1.2),B6/B5,IF(B6/B5<0.8,0.8,IF(B6/B51.2,1.2))) "Dave F" wrote: =IF(AND(B6/B5.8,B6/B5<1.2,B6/B5,IF(B6/B5<.8,.8,IF(B6/B51.2,1.2))) Dave -- Brevity is the soul of wit. "Jdd561" wrote: B5 100000 B6 150000 I'm trying to work out a ratio of the above data that fits within specific limits. I need to have B6/B5, but only if the result is greater than .8 or less than 1.2. If this ratio is less that .8, I want .8 to be the answer. If the result is greater than 1.2, I need 1.2 to be the answer. In english, my IF statement is below IF (B6/B5).8 and <1.2, then B6/B5 IF (B6/B5)<.8, then .8 IF (B6/B5)1.2, then 1.2 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate within a range?
=MIN(MAX(A1/A2,0.8),1.2)
"Jdd561" wrote: B5 100000 B6 150000 I'm trying to work out a ratio of the above data that fits within specific limits. I need to have B6/B5, but only if the result is greater than .8 or less than 1.2. If this ratio is less that .8, I want .8 to be the answer. If the result is greater than 1.2, I need 1.2 to be the answer. In english, my IF statement is below IF (B6/B5).8 and <1.2, then B6/B5 IF (B6/B5)<.8, then .8 IF (B6/B5)1.2, then 1.2 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate within a range?
using your cell references
=MIN(MAX(B6/B5,0.8),1.2) "Jdd561" wrote: B5 100000 B6 150000 I'm trying to work out a ratio of the above data that fits within specific limits. I need to have B6/B5, but only if the result is greater than .8 or less than 1.2. If this ratio is less that .8, I want .8 to be the answer. If the result is greater than 1.2, I need 1.2 to be the answer. In english, my IF statement is below IF (B6/B5).8 and <1.2, then B6/B5 IF (B6/B5)<.8, then .8 IF (B6/B5)1.2, then 1.2 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate within a range?
Maybe another way:
=MAX(MIN(B6/B5,1.2),0.8) -- HTH :) Dana DeLouis Windows XP & Office 2003 "Jdd561" wrote in message ... B5 100000 B6 150000 I'm trying to work out a ratio of the above data that fits within specific limits. I need to have B6/B5, but only if the result is greater than .8 or less than 1.2. If this ratio is less that .8, I want .8 to be the answer. If the result is greater than 1.2, I need 1.2 to be the answer. In english, my IF statement is below IF (B6/B5).8 and <1.2, then B6/B5 IF (B6/B5)<.8, then .8 IF (B6/B5)1.2, then 1.2 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OFFSET and range addresses | Excel Discussion (Misc queries) | |||
| Range name problems | Excel Discussion (Misc queries) | |||
| UDF is updateing cells on another sheet with count from current sheet. | Excel Discussion (Misc queries) | |||
| Calculate Date range | Excel Worksheet Functions | |||
| Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions | |||
Hybrid Mode
