Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 26th 06, 05:35 PM posted to microsoft.public.excel.misc
Pheasant Plucker®
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

Hi there,

I have a spreadsheet where the percentage score will indicate a Pass or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to achieve
this?

--
Regards,
-pp-



  #2   Report Post  
Old January 26th 06, 06:01 PM posted to microsoft.public.excel.misc
Pogue
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

I'd do it in 2 steps:

Put this formula in cell I22
=IF(B3="","",IF(B3=0.8,"Pass","Fail"))

Then you want to use Conditional Formatting.
Click on "Format" in the menu. Then select "Conditional Formatting"
In the first box, select "Formula is"
In the next box type =b3=.8
Then click the format button, and click on "color" select green (you can
also bold it to make the green stand out)

Now click "Add", and you'll get another format opportunity,
In the first box, select "Formula is"
In the next box type =b3<.8
Then click Format, Color, choose Red.

Good luck!

"Pheasant PluckerĀ®" wrote:

Hi there,

I have a spreadsheet where the percentage score will indicate a Pass or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to achieve
this?

--
Regards,
-pp-



  #3   Report Post  
Old January 26th 06, 06:07 PM posted to microsoft.public.excel.misc
Bill Martin
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

First set the I22 cell formula as follows:

[I22] = if(h20 0.80,"Pass","Fail")

Then to set the colors, first format I22 to have the green text you want. Then
select the cell and click on FormatConditionalFormatting and follow your nose
through the options to make the text Red if the word "Fail" is in the cell.

Good luck...

Bill
----------------------------------
Pheasant Plucker® wrote:
Hi there,

I have a spreadsheet where the percentage score will indicate a Pass or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to achieve
this?

  #4   Report Post  
Old January 26th 06, 06:12 PM posted to microsoft.public.excel.misc
David Biddulph
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

"Pheasant Plucker®" wrote in message
...
Hi there,

I have a spreadsheet where the percentage score will indicate a Pass or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text

coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to achieve
this?


In I22 put the formula =IF(H20="","",IF(H20<80%,"FAIL","PASS"))
Then in I22 select Format/ Conditional Formatting
Set the condition Cell Value is equal to FAIL, use the pattern tab for your
red background and the Font tab to make your text bold.
Use the Add button to add another condition & set up your PASS to green &
bold in the same way.
--
David Biddulph


  #5   Report Post  
Old January 26th 06, 07:10 PM posted to microsoft.public.excel.misc
Pheasant Plucker®
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

Great!

Thanks to all who replied - this ng is the greatest and I am learning all
the time! :-)

One last question though...sorry.

If none of the results have not yet been filled in then cell H20 displays 0%

This obviously now displays FAIL in black, bolded text in a Red cell -
technically this is correct because H20 is below 80% but as no data has been
entered yet technically it cannot be a Fail ;^)

How do I prevent the FAIL appearing in this red cell if H20 is displaying 0%
because no data has been entered yet?

Ideally I would like to have no data in the cell but have it coloured Yellow
to indicate that it is an important cell but no data has yet been entered
into the spreadsheet.

If it helps the cells involved are all formatted as Percentage and their
formulas are as follows;

H20=H17+H19

H19 has no formula currently as it is 'under development' - maybe a question
for another time!

H17=SUM(C17:G17)/100

C17=IF(C12="","",AVERAGEA(C12:C16))
D17=IF(D12="","",AVERAGEA(D1216))
etc.

Thanks again,
-=pp=-


"David Biddulph" <david(dot)biddulph(at)baesystems.com wrote in message
...
"Pheasant Plucker®" wrote in message
...
Hi there,

I have a spreadsheet where the percentage score will indicate a Pass or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text

coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text

coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then

just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to achieve
this?


In I22 put the formula =IF(H20="","",IF(H20<80%,"FAIL","PASS"))
Then in I22 select Format/ Conditional Formatting
Set the condition Cell Value is equal to FAIL, use the pattern tab for

your
red background and the Font tab to make your text bold.
Use the Add button to add another condition & set up your PASS to green &
bold in the same way.
--
David Biddulph







  #6   Report Post  
Old January 27th 06, 01:50 AM posted to microsoft.public.excel.misc
Pogue
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

Easily solved. Our formula had assumed the cell would be blank - it instead
carries a 0 value. Change to:
=IF(H20=0,"",IF(H20<80%,"FAIL","PASS"))

"Pheasant PluckerĀ®" wrote:

Great!

Thanks to all who replied - this ng is the greatest and I am learning all
the time! :-)

One last question though...sorry.

If none of the results have not yet been filled in then cell H20 displays 0%

This obviously now displays FAIL in black, bolded text in a Red cell -
technically this is correct because H20 is below 80% but as no data has been
entered yet technically it cannot be a Fail ;^)

How do I prevent the FAIL appearing in this red cell if H20 is displaying 0%
because no data has been entered yet?

Ideally I would like to have no data in the cell but have it coloured Yellow
to indicate that it is an important cell but no data has yet been entered
into the spreadsheet.

If it helps the cells involved are all formatted as Percentage and their
formulas are as follows;

H20=H17+H19

H19 has no formula currently as it is 'under development' - maybe a question
for another time!

H17=SUM(C17:G17)/100

C17=IF(C12="","",AVERAGEA(C12:C16))
D17=IF(D12="","",AVERAGEA(D1216))
etc.

Thanks again,
-=pp=-


"David Biddulph" <david(dot)biddulph(at)baesystems.com wrote in message
...
"Pheasant PluckerĀ®" wrote in message
...
Hi there,

I have a spreadsheet where the percentage score will indicate a Pass or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text

coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text

coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then

just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to achieve
this?


In I22 put the formula =IF(H20="","",IF(H20<80%,"FAIL","PASS"))
Then in I22 select Format/ Conditional Formatting
Set the condition Cell Value is equal to FAIL, use the pattern tab for

your
red background and the Font tab to make your text bold.
Use the Add button to add another condition & set up your PASS to green &
bold in the same way.
--
David Biddulph






  #7   Report Post  
Old January 27th 06, 06:47 AM posted to microsoft.public.excel.misc
Pheasant Plucker®
 
Posts: n/a
Default Pass or Fail (green or red) criteria for cell?

Easy when you know how - thanks Pogue!

I was trying to be too clever and using an extra entry in Conditional
Formatting...

Thanks again to all who replied - it is much appreciated.

Kind regards,
-=pp=-

"Pogue" wrote in message
...
Easily solved. Our formula had assumed the cell would be blank - it

instead
carries a 0 value. Change to:
=IF(H20=0,"",IF(H20<80%,"FAIL","PASS"))

"Pheasant Plucker®" wrote:

Great!

Thanks to all who replied - this ng is the greatest and I am learning

all
the time! :-)

One last question though...sorry.

If none of the results have not yet been filled in then cell H20

displays 0%

This obviously now displays FAIL in black, bolded text in a Red cell -
technically this is correct because H20 is below 80% but as no data has

been
entered yet technically it cannot be a Fail ;^)

How do I prevent the FAIL appearing in this red cell if H20 is

displaying 0%
because no data has been entered yet?

Ideally I would like to have no data in the cell but have it coloured

Yellow
to indicate that it is an important cell but no data has yet been

entered
into the spreadsheet.

If it helps the cells involved are all formatted as Percentage and their
formulas are as follows;

H20=H17+H19

H19 has no formula currently as it is 'under development' - maybe a

question
for another time!

H17=SUM(C17:G17)/100

C17=IF(C12="","",AVERAGEA(C12:C16))
D17=IF(D12="","",AVERAGEA(D1216))
etc.

Thanks again,
-=pp=-


"David Biddulph" <david(dot)biddulph(at)baesystems.com wrote in message
...
"Pheasant Plucker®" wrote in message
...
Hi there,

I have a spreadsheet where the percentage score will indicate a Pass

or
Fail.

Is there a way that I can automatically input the words Pass or Fail
depending on what the percentage score actually is?

For example;

If cell H20 indicates 80% or above then input the word Pass (text

coloured
black and bold) in cell I22 coloured green?

If cell H20 indicates less than 80% then input the word Fail (text
coloured
black and bold) in cell I22 coloured Red?

If the black text in a coloured box is too difficult to achieve then

just
the word Pass in Green or Fail in Red would suffice.

I22 to have no colour if no result present.

Does this even make sense and if it does is there an easy way to

achieve
this?

In I22 put the formula =IF(H20="","",IF(H20<80%,"FAIL","PASS"))
Then in I22 select Format/ Conditional Formatting
Set the condition Cell Value is equal to FAIL, use the pattern tab for

your
red background and the Font tab to make your text bold.
Use the Add button to add another condition & set up your PASS to

green &
bold in the same way.
--
David Biddulph










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
Criteria problems in the DSUM function AA Excel Worksheet Functions 5 January 10th 06 10:16 PM
Returning Results Based on Two Criteria [email protected] Excel Worksheet Functions 7 October 23rd 05 02:53 PM
sorting more than 3 keys Brooke Excel Discussion (Misc queries) 3 June 18th 05 04:52 AM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 01:37 AM
pass fail Tricia New Users to Excel 3 January 20th 05 03:45 PM


All times are GMT +1. The time now is 10:07 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017