Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill a cell based on a condition being met
Hi, I am trying to develop a formula that will only fill the final grade of a
student in the event that there are two grades allocated in the results column. I have the formula to assign the letter grade worked out, but because the value of the source cell is '0', Excel automatically assigns a 'F' in the cell (B13) (and rightly so as the formula is simply doing what it is told). I have 2 seperate scores, one each stored in cells D13 and E13, these are summed to give a total score out of 100 and this result is stored in cell C13. Based on the following formula : =IF(C1384.99,"HD",IF(C1374.99,"D",IF(C1364.99," C",IF(C1349.99,"P",IF(C1342.99,"PC",IF(O7=0,"F" )))))) I need the result in the format of a letter grade in cell B13 based on the formula above, however I only want a value displayed in cell B13 if the criteria below is met: A grade is assigned in each of cells D13 and E13. If anyone has an answer it would be greatly appreciated. Brian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill a cell based on a condition being met
Hi!
If I understand correctly: =IF(COUNTA(D13:E13)<2,"",IF(AND(ISNUMBER(O7),O7<43 ),"F",LOOKUP(C13,{43;50;65;75;85},{"PC";"P";"C";"D ";"HD"}))) Biff "confused teacher" wrote in message ... Hi, I am trying to develop a formula that will only fill the final grade of a student in the event that there are two grades allocated in the results column. I have the formula to assign the letter grade worked out, but because the value of the source cell is '0', Excel automatically assigns a 'F' in the cell (B13) (and rightly so as the formula is simply doing what it is told). I have 2 seperate scores, one each stored in cells D13 and E13, these are summed to give a total score out of 100 and this result is stored in cell C13. Based on the following formula : =IF(C1384.99,"HD",IF(C1374.99,"D",IF(C1364.99," C",IF(C1349.99,"P",IF(C1342.99,"PC",IF(O7=0,"F" )))))) I need the result in the format of a letter grade in cell B13 based on the formula above, however I only want a value displayed in cell B13 if the criteria below is met: A grade is assigned in each of cells D13 and E13. If anyone has an answer it would be greatly appreciated. Brian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill a cell based on a condition being met
Thanks Biff, it works.
You dont happen to know how to get a formula to copy down automatically when you enter new data in the cell. I have a cohort of students and the numbers vary considerably, it would be so much easier if i could just enter the raw data and the formulas copied down as necessary. thanks Brian "Biff" wrote: Hi! If I understand correctly: =IF(COUNTA(D13:E13)<2,"",IF(AND(ISNUMBER(O7),O7<43 ),"F",LOOKUP(C13,{43;50;65;75;85},{"PC";"P";"C";"D ";"HD"}))) Biff "confused teacher" wrote in message ... Hi, I am trying to develop a formula that will only fill the final grade of a student in the event that there are two grades allocated in the results column. I have the formula to assign the letter grade worked out, but because the value of the source cell is '0', Excel automatically assigns a 'F' in the cell (B13) (and rightly so as the formula is simply doing what it is told). I have 2 seperate scores, one each stored in cells D13 and E13, these are summed to give a total score out of 100 and this result is stored in cell C13. Based on the following formula : =IF(C1384.99,"HD",IF(C1374.99,"D",IF(C1364.99," C",IF(C1349.99,"P",IF(C1342.99,"PC",IF(O7=0,"F" )))))) I need the result in the format of a letter grade in cell B13 based on the formula above, however I only want a value displayed in cell B13 if the criteria below is met: A grade is assigned in each of cells D13 and E13. If anyone has an answer it would be greatly appreciated. Brian |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill a cell based on a condition being met
The only way I know of is to "key" the formula on a certain cell. If that
"key" cell has data entered into it then the formula does its thing but until the key cell is filled, the formula returns a blank. That would mean you'd have to copy the formula beyond the current end of data in anticipation of future data entry. It's simple enough, just another IF: =IF(A1="","",do_this_when_A1_is_filled) Biff "confused teacher" wrote in message ... Thanks Biff, it works. You dont happen to know how to get a formula to copy down automatically when you enter new data in the cell. I have a cohort of students and the numbers vary considerably, it would be so much easier if i could just enter the raw data and the formulas copied down as necessary. thanks Brian "Biff" wrote: Hi! If I understand correctly: =IF(COUNTA(D13:E13)<2,"",IF(AND(ISNUMBER(O7),O7<43 ),"F",LOOKUP(C13,{43;50;65;75;85},{"PC";"P";"C";"D ";"HD"}))) Biff "confused teacher" wrote in message ... Hi, I am trying to develop a formula that will only fill the final grade of a student in the event that there are two grades allocated in the results column. I have the formula to assign the letter grade worked out, but because the value of the source cell is '0', Excel automatically assigns a 'F' in the cell (B13) (and rightly so as the formula is simply doing what it is told). I have 2 seperate scores, one each stored in cells D13 and E13, these are summed to give a total score out of 100 and this result is stored in cell C13. Based on the following formula : =IF(C1384.99,"HD",IF(C1374.99,"D",IF(C1364.99," C",IF(C1349.99,"P",IF(C1342.99,"PC",IF(O7=0,"F" )))))) I need the result in the format of a letter grade in cell B13 based on the formula above, however I only want a value displayed in cell B13 if the criteria below is met: A grade is assigned in each of cells D13 and E13. If anyone has an answer it would be greatly appreciated. Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
I want to fill the cell color based upon the other cell condition | Excel Discussion (Misc queries) | |||
How do I set a cell value based on a formula in another cell? | Excel Discussion (Misc queries) | |||
I am trying to color fill a cell based on specific criteria | Excel Discussion (Misc queries) | |||
make a cell empty based on condition | Charts and Charting in Excel |