Home |
Search |
Today's Posts |
#1
|
|||
|
|||
The IF function
Trying to use the IF function to get a cell to generate a "grade" based on a
number of points, e.g.: IF the contents of B1 are less than 40, then B2 will read "A" IF the contents of B1 are greater than or equal to 41, BUT less than 71, then B2 will read "B" IF the contents of B1 are greater than or equal to 71, then B2 will read "C". Can't get this to work. Am currently using three cells with IF formula, with the idea that they'll either read A or xxx, B or xxx or C or xxx (to accommodate the true or false requirements of the IF function), but I think I'm going about it ALL wrong. What I really want to do, basically, is get a formula that reads the number in a single cell, and gives me one of three letter values in another cell based on the numerical value of the first cell. This is driving me **bats**. Any suggestions? |
#2
|
|||
|
|||
if(B1<40;"A";if(AND(B1=41;B<71);"B";"C")) This should work for you... -- ehntd ------------------------------------------------------------------------ ehntd's Profile: http://www.excelforum.com/member.php...o&userid=15865 View this thread: http://www.excelforum.com/showthread...hreadid=277667 |
#3
|
|||
|
|||
On Fri, 12 Nov 2004 03:26:01 -0800, "Minesalargejackdaniels"
m wrote: Trying to use the IF function to get a cell to generate a "grade" based on a number of points, e.g.: IF the contents of B1 are less than 40, then B2 will read "A" IF the contents of B1 are greater than or equal to 41, BUT less than 71, then B2 will read "B" IF the contents of B1 are greater than or equal to 71, then B2 will read "C". Can't get this to work. Am currently using three cells with IF formula, with the idea that they'll either read A or xxx, B or xxx or C or xxx (to accommodate the true or false requirements of the IF function), but I think I'm going about it ALL wrong. What I really want to do, basically, is get a formula that reads the number in a single cell, and gives me one of three letter values in another cell based on the numerical value of the first cell. This is driving me **bats**. Any suggestions? You leave undefined the situation where B1=40. I have assumed that that grade will be a "A". If that assumption is incorrect, you will need to change the formula. =IF(B1<41,"A",IF(B1<71,"B","C")) Of course, this will also give an "A" if there is no entry in B1. You may want to test for that, but how to test depends on how the points gets into B1. If they are entered directly, and/or if the points must always be greater than zero, then you can test for a number greater than 0. If the number is computed by a formula, then the testing might depend on the formula used. But something like: =IF(B1="","",IF(B1<41,"A",IF(B1<71,"B","C"))) Finally, nesting IF's (or any function) can only be done to seven levels. So if you are looking to expand this, take a look at the VLOOKUP function. --ron |
#4
|
|||
|
|||
Hi
=CHOOSE(MATCH(B1,{1,41,71},1),"A","B","C") (Values in B1 must be 0, otherwise an error is returned) -- Arvi Laanemets (When sending e-mail, use address arvil<Attarkon.ee) "Minesalargejackdaniels" m wrote in message ... Trying to use the IF function to get a cell to generate a "grade" based on a number of points, e.g.: IF the contents of B1 are less than 40, then B2 will read "A" IF the contents of B1 are greater than or equal to 41, BUT less than 71, then B2 will read "B" IF the contents of B1 are greater than or equal to 71, then B2 will read "C". Can't get this to work. Am currently using three cells with IF formula, with the idea that they'll either read A or xxx, B or xxx or C or xxx (to accommodate the true or false requirements of the IF function), but I think I'm going about it ALL wrong. What I really want to do, basically, is get a formula that reads the number in a single cell, and gives me one of three letter values in another cell based on the numerical value of the first cell. This is driving me **bats**. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function in XL or in VBA for XL that pulls numeric digits from a t | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
change function variable prompts?? | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions | |||
SUMIF(AND) FUNCTION | Excel Worksheet Functions |