Solved

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

Posted on 2016-10-21
8
41 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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