Cell Rule HELP...

Question

Level 1

4 points

Cell Rule HELP...

I have created a simple spreadsheet that has Yes or No answers, I have also created a formulae to show this as a percentage using the following formulae and adding the data format as a percentage.

COUNTIF(B,"Yes")÷(COUNTIF(B,"No")+COUNTIF(B,"Yes"))

However I am also trying to add a rule to that cell to show either text or cell background as green if the % is over 33 and as red if the % is less than 33 but the rule is not working...

Can anyone offer advice please...

MacBook Air 15″

Posted on Aug 20, 2023 8:06 AM

Reply

Answer 1

6x6

Level 6

19,317 points

Aug 20, 2023 8:51 AM in response to swinder

Hello swinder,

Even though your calculation is a percentage, the conditional highlighting has to be done on the actual number, 0.33 in your case :

Reply

Answer 2

6x6

Level 6

19,317 points

Aug 20, 2023 11:55 AM in response to swinder

I suspect you have a problem with the data, probably one or more entries that are mistyped. Which is odd since you end up with a 100% total (52% + 48%). Use this example instead:

Notice that in the denominator, the function COUNTA is used to simplify the formula.

Reply

Answer 3

swinder Author

Level 1

4 points

Aug 20, 2023 10:26 AM in response to 6x6

Thanks for your reply - that’s great and appreciated - I thought it would be something reasonably simple.

Please can I ask you another query ?!?

I have some data as follows:

Male Pass

Female Pass

Male Fail

Female Fail

I am putting this data into a chart and need to know percentages (Pass Rate & Fail Rate) in the chart I do not need to show the rate for Male & Female just the total Pass % and Fail %.

At the moment in the data I have done a total of 79 tests with a total of 44 passes so the percentage should be 55% HOWEVER I am getting a pass rate of 52% and fail rate of 48% - I am asking the data to show male pass + female pass and also male fail + female fail and asking to show as % in the chart but it’s giving me wrong figures as above.

Reply

Answer 4

swinder Author

Level 1

4 points

Aug 20, 2023 12:52 PM in response to 6x6

Thanks again - I have attached the image below as I have the data shown pulled from my main spreadsheet.

is there a formulae suitable to do what you suggest from the data as shown in the image ?? As the numbers change constantly as I add data to the main spreadsheet….

Appreciate any help.

Reply