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.

 Fill a cell based on a condition being met
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Fill a cell based on a condition being met

#1
July 5th 06, 05:33 AM posted to microsoft.public.excel.worksheet.functions
 confused teacher external usenet poster 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(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
#2
July 5th 06, 07:39 AM posted to microsoft.public.excel.worksheet.functions
 Biff external usenet poster 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(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
July 5th 06, 07:57 AM posted to microsoft.public.excel.worksheet.functions
 confused teacher external usenet poster 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(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
July 5th 06, 08:29 AM posted to microsoft.public.excel.worksheet.functions
 Biff external usenet poster 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(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 Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 03:20 PM.