Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with this conditional IF statement C-Dawg 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 02: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 09: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 02:01 PM


All times are GMT +1. The time now is 06:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"