• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find values in a range and assign differtn numbers to them?

M

mjs591

New Member
What fomula will tell me if the values in column B(DIFF) fall into one of these categoires, Between 0.00 and 0.25 is .25, Between 0.251 and 0.50 is .50. Between 0.51 and 0.75 is .75. Greater than 0.751 is 1.00


ST Diff

Indiana 0.50

Ohio 1.05

Ohio 1.00

Ohio 0.25


Thank you
 
The IF function may.....


=IF(B23<=0.25,".25",IF(B23<=0.5,".50",IF(B23<=0.75,".75",IF(B23>=0.751,"1.00"))))


'B23' would be whatever cell contains the Diff value
 
Thank you- how can I moify the formula to look for ranges, ie between .25 and .49 for a value then .50 and .74, etc. Can I also add negative values in the formula ie lookign for between -.25 and -.50?
 
it technically is looking at ranges... if the value is not less than or equal to .25, it'll look to see if the value is less than or equal to .5. That establishes the ranges <=.25 will = .25, anything >.25 to <.50 will = .5 ...maybe im not understanding your first question. (do you want a value between .25 and .49 to return something besides .25? if so, you would just manipulate the ranges and criteria. are you familiar with the IF function?)


to add negative ranges, you would just add more arguments to establish the desired criteria.


TIP: i find it easier to read to build the formula 'in order' (ie:smallest to largest or vice versa) so IFWM, i'd start with the negative range criteria first then work my way up.
 
You can use VLOOKUP in your case with range_lookup option.


You can refer the link below for more detailed explanation with an example:

http://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/
 
You must log in or register to reply here.
Back
Top