If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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(C13>84.99,"HD",IF(C13>74.99,"D",IF(C13>64.99," C",IF(C13>49.99,"P",IF(C13>42.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 
Ads 
#2




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(C13>84.99,"HD",IF(C13>74.99,"D",IF(C13>64.99," C",IF(C13>49.99,"P",IF(C13>42.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




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(C13>84.99,"HD",IF(C13>74.99,"D",IF(C13>64.99," C",IF(C13>49.99,"P",IF(C13>42.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




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(C13>84.99,"HD",IF(C13>74.99,"D",IF(C13>64.99," C",IF(C13>49.99,"P",IF(C13>42.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 >> >> >> 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Help with this conditional IF statement  CDawg  Excel Discussion (Misc queries)  3  May 15th 06 06:01 PM 
I want to fill the cell color based upon the other cell condition  sri  Excel Discussion (Misc queries)  4  January 12th 06 01:47 PM 
How do I set a cell value based on a formula in another cell?  dingy101  Excel Discussion (Misc queries)  1  November 21st 05 08:51 AM 
I am trying to color fill a cell based on specific criteria  jglen  Excel Discussion (Misc queries)  1  May 27th 05 05:30 PM 
make a cell empty based on condition  mpierre  Charts and Charting in Excel  2  December 29th 04 01:01 PM 