0

I have a table like this:

Document1   Document2   Document3   Document4   Document5   Document6   Document7   Document8   % Wrong documents
Expired     Expired     31/12/2020  30/12/2020  Accepted    Accepted    N/A         24/11/2021  
24/12/2020  1/1/2021    30/12/2020  30/12/2020  Accepted    Empty       N/A         1/2/2022    
Expired     Expired     Rejected    2/7/2021    Accepted    Accepted    N/A         N/A 
Expired     Expired     Incomplete  Incomplete  Accepted    Empty       N/A         N/A 
7/1/2021    2/1/2021    Expired     1/7/2021    Accepted    Accepted    N/A         Rejected    
Expired     Expired     Expired     1/1/2021    Accepted    Accepted    N/A         N/A

enter image description here And I was wondering if there's any way to make a conditional formatting with a formula calculating the percentage of "bad" documents (Rejected,Incomplete,Expired,Empty) of the total of documents, but with the plus that the "N/A" documents hasn't have to be counted to make the percentage of all documents, which function do I need to use to achieve that.

If I have explained myself wrong, which is probably what happened, ask me in the comments please, thanks!

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thank you for posting your data as text. *I was wondering if there's any way to make a conditional formatting with a formula calculating the percentage of "bad" documents* Yes, there is. Use the results in that column to conditionally format the relevant cells. If you have a specific formula question, please post what you have tried, and where you have run into problems. Might help to read the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – Ron Rosenfeld Dec 11 '20 at 13:26
  • Thank you for your answer, I'm not really familiarized with Excel formulas, just wanted to know which function do I have to use, I'll learn myself how to develop it. I was looking for some kind of `SUMIFS()` but I don't really want to `SUM()`, maybe with a `COUNTIFS()` you think I can achieve my purpose? –  Dec 11 '20 at 14:33
  • I would be using `SUM` and `COUNTIF` with an array constant for the first `COUNTIF` function criteria, and `"<>N/A" for the second `COUNTIF` function criterion – Ron Rosenfeld Dec 11 '20 at 16:26
  • Ok, i'll try to process that, thanks mate! –  Dec 11 '20 at 16:34
  • I finally did it, `=SUM(COUNTIF($F5:$M5; {"*Pending*";"*Rejected*";"*Expired*";"*Empty*"})) / COUNTIF($F5:$M5;"<>*N/A*")`, but when I tried to do a conditional formatting, I get this error: `You may not use reference operators such as unions intersections and ranges, array constants or the lambda function`. Do you know why I get it and how can I solve it? Thanks! –  Dec 14 '20 at 10:38
  • You get error because you are using an array constant. You can avoid it either by basing your CF on the contents of column I, or expanding the formula so as to avoid using the array constant. – Ron Rosenfeld Dec 14 '20 at 11:46
  • Hi Ron thank you for your time, I don't know if you did it, but if so, can you remove that dislike from the question? As I said I just wanted to know which function to use, didn't want you to do the formula for me, and I don't really think my question was that bad. –  Dec 16 '20 at 09:02

0 Answers0