Remember Me?

#1
July 5th 06, 05:33 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 12
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
July 5th 06, 07:39 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,688
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
July 5th 06, 07:57 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 12
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
July 5th 06, 08:29 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,688
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
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM sri Excel Discussion (Misc queries) 4 January 12th 06 01:47 PM dingy101 Excel Discussion (Misc queries) 1 November 21st 05 08:51 AM jglen Excel Discussion (Misc queries) 1 May 27th 05 05:30 PM mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM

All times are GMT +1. The time now is 02:13 PM.