Solved

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

Posted on 2016-10-21
8
46 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
Industry Leaders: 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 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to consolidate .ods files in one sheet 30 43
Embed XL WS and Objects into a PPTX 8 34
Custom fill series 12 40
VBA Lookup Problem 2 13
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

751 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