Solved

Excel - If every cell in row has "Done" then give a 100%

Posted on 2016-10-21
8
38 Views
Last Modified: 2016-10-26
Hi experts.
Looking for a conditional formatting that looks for the word "Done" in every cell in a range in a row.
If all the cells have the word "Done" then we can put a 100% text in the desired cell at the end of the row.

Thanks Experts.
Chris
0
Comment
Question by:chris pike
  • 5
  • 2
8 Comments
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41854615
in the place where you want the percentage do this:

1. format the cell as percent for visual sake.
2. you want to countif all blocks that say done, then divide that into all the blocks with values:

=COUNTIF(<<PUT RANGE IN HERE>>,"Done")/COUNTA(<<PUTRANGE IN HERE>>)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41854691
This isn't handled with Conditional Formatting, but can be evaluated in a couple of different methods:

1) Compare the number of cells with "Done" against the number of rows in the range.
=IF(COUNTIF($B$2:$B$10,"Done")=ROWS($B$2:$B$10),"100%","")

2) Using an array function ([Ctrl]+[Shift]+[Enter] to enter the formula), check if all cells in range = "Done"
=IF(AND($B$2:$B$10="Done"),"100%","")

3) Generate a percentage result by dividing number of cells with "Done" against all cells in range.  
Note that the previously-suggested solution only works if every cell in the range has a value; if there are any blanks the result will be incorrect.
=COUNTIF($B$2:$B$10,"Done")/ROWS($B$2:$B$10)

See attached workbook for examples of all three.

-Glenn
EE-Q_28978105.xlsx
1
 

Author Comment

by:chris pike
ID: 41857501
Thanks Glen,\
I think I should have given additional info..  I need done in columns, not rows.
done-100-.JPG
0
 

Author Comment

by:chris pike
ID: 41857505
Thanks Evan,
 I think we are having the same problem, with rows instead of columns.
This is what I am looking for.
done-100-.JPG
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41857671
My oversight; sorry.  

The solution method is the same, just need to transpose the ranges to look up AND change the quantity of ROWS to COLUMNS.  Using your example any these would work:
1) Compare the number of cells with "Done" against the number of rows in the range.
=IF(COUNTIF($B20:$F20,"Done")=COLUMNS($B20:$F20),"100%","")

2) Using an array function ([Ctrl]+[Shift]+[Enter] to enter the formula), check if all cells in range = "Done"
=IF(AND($B20:$F20="Done"),"100%","")

3) Generate a percentage result by dividing number of cells with "Done" against all cells in range.  
=COUNTIF($B20:$F20,"Done")/COLUMNS($B20:$F20)

I note that your new example displays a percentage regardless of whether it is equal to 100%; this differs from your intial question.  If you do indeed want the percentage value, then only the third solution is appropriate.

I've revised the example workbook with a new sheet showing each of these possible solutions.

Regards,
-Glenn
EE-Q_28978105.xlsx
0
 

Author Comment

by:chris pike
ID: 41860496
Thanks Glen
I love your style of helping. (offering three different styles) Awesome.

However, I could not get it working.
If it is just TEXT then it works, but in my case, there is a formula in the DONE cell already that is putting DONE into that cell.

How do we make it work if there is a formula already in there???
Thanks so much/
Chris
0
 

Author Comment

by:chris pike
ID: 41860522
Thanks Evan, needed a little more help on this one.
0
 

Author Closing Comment

by:chris pike
ID: 41860528
Thanks Glenn, works great if only TEXT is in cell. As it was what I
I will ask a second question to make it work with cells that already have a formula in them.
Thanks so much.
Chris
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now