A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Fill a cell based on a condition being met



 
 
Thread Tools Display Modes
  #1  
Old July 5th 06, 05:33 AM posted to microsoft.public.excel.worksheet.functions
confused teacher
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(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  
Old July 5th 06, 07:39 AM posted to microsoft.public.excel.worksheet.functions
Biff
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(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  
Old July 5th 06, 07:57 AM posted to microsoft.public.excel.worksheet.functions
confused teacher
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(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  
Old July 5th 06, 08:29 AM posted to microsoft.public.excel.worksheet.functions
Biff
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(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

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

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.


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