Solved

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

Posted on 2016-10-21
8
44 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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