Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-21
8
Medium Priority
?
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

670 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