Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF Function
I am trying to write an if function where it will return letter A if it is <
50, letter B if it is 50 and <75 and letter C if it is 75 and <100. Does anyone know how to write an if function using ranges? Nothing I try seems to work Thanks! |
#2
|
|||
|
|||
The following works for me.
=IF(A1<50,"A",IF(AND(A150,A1<75),"B",IF(AND(A1 75,A1<100),"C","Undefined"))) I included "Undefined" as the last option since the formula would not know what to do with the numbers 50, 75, 100 or anything greater than 100. Not sure of your application but you might want to use = signs where appropriate. Another option would be to use a vlookup formula. Create a small table that includes the starting value for each range and the appropriate marker. Let's assume that 100 = C, 75 = B and 50 would equal A. The As would start on 0, the Bs would start on 51 and the Cs would start on 76. Col E F Row 1 0 A Row 2 51 B Row 3 76 C The formula vlookup(A1, E1:F3,2) would return the proper letter. This is an inexact lookup. When you can create a table like this, this would be the preferred method since you can easily change the ranges and the markers (A,B,C). When I use the first approach, I rarely would hard code the markers or the ranges in the formula. Instead I'd use cell references so that I can change the markers and ranges in a table and all of the formulas would update. Hope that helps. John |
#3
|
|||
|
|||
Hi
Just to improve on waxwings example. =IF(A1<50,"A",IF(A1<75,"B",IF(A1<100,"C","Undefine d"))) HTH Michael Mitchelson =IF(A1<50,"A",IF(AND(A150,A1<75),"B",IF(AND(A1 75,A1<100),"C","Undefined"))) I included "Undefined" as the last option since the formula would not know what to do with the numbers 50, 75, 100 or anything greater than 100. Not sure of your application but you might want to use = signs where appropriate. Another option would be to use a vlookup formula. Create a small table that includes the starting value for each range and the appropriate marker. Let's assume that 100 = C, 75 = B and 50 would equal A. The As would start on 0, the Bs would start on 51 and the Cs would start on 76. Col E F Row 1 0 A Row 2 51 B Row 3 76 C The formula vlookup(A1, E1:F3,2) would return the proper letter. This is an inexact lookup. When you can create a table like this, this would be the preferred method since you can easily change the ranges and the markers (A,B,C). When I use the first approach, I rarely would hard code the markers or the ranges in the formula. Instead I'd use cell references so that I can change the markers and ranges in a table and all of the formulas would update. Hope that helps. John |
#4
|
|||
|
|||
Good point Michael. I choose the "And" approach because it wasn't
clear in the original message if all values below 100 were included. And allows the user to disregard certain values such as "50" which was how the request was written but probably not what the user intended. - John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |