| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Returning a value for a number in a range
With my lacklustre Excel skills, I am struggling to build a table that allows
me to score a number based on its location in a set of ranges. For example, I want to score any percentage between 90% and 110% with a 3, anything between 80% and 90%/ 110% and 120% with a 2, and so on. Any ideas on how I can do this easily? |
|
#2
|
|||
|
|||
|
One way:
=MIN(3,4-(CEILING(ABS(1-A1)*10,1))) another: =LOOKUP(ABS(1-A1),{0,3;0.1,2;0.2,1;0.3,0}) In article , "Derek" wrote: With my lacklustre Excel skills, I am struggling to build a table that allows me to score a number based on its location in a set of ranges. For example, I want to score any percentage between 90% and 110% with a 3, anything between 80% and 90%/ 110% and 120% with a 2, and so on. Any ideas on how I can do this easily? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
| Formula for displaying the lowest number of a range? | Excel Worksheet Functions | |||
| Need help Returning the cell addy/index of the first non-zero number | Excel Worksheet Functions | |||
| How to add one number to a range of numbers | Excel Worksheet Functions | |||
| Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions | |||
Hybrid Mode
