Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Cell already has formula and can not apply a second formula.

Posted on 2016-10-26
16
Medium Priority
?
50 Views
Last Modified: 2016-10-27
I have three great formulas the looks across columns for DONE in cells and then gives a percentage in the last column.
The problem is, all the cells already have a formula in them that is putting the DONE in the cell.

Here are the formulas. (all work great if it is just TEXT, but not if a formula is already in the cell)
=IF(COUNTIF($B20:$F20,"Done")=COLUMNS($B20:$F20),"100%","")

=IF(AND($B20:$F20="Done"),"100%","")

=COUNTIF($B20:$F20,"Done")/COLUMNS($B20:$F20)

Thanks
Chris
0
Comment
Question by:chris pike
  • 8
  • 8
16 Comments
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41860615
Have you tried using the TEXT function  with the other formulas?
0
 

Author Comment

by:chris pike
ID: 41860633
Yes they all work.
I tried all three of these formulas for text. They are all good. I prefer the last one because it gives a percentage of completeness. ie, 80% or 70% and 100% if every cell in range has DONE
Thanks
Chris
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41860645
I meant using all formulas involved with the Text Function.
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: 41860658
No I have not, I don't know how to do that.
Any help is appreciated.
Thanks
Chris
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41860678
It's just an idea but can you attach an example of your workbook.
0
 

Author Comment

by:chris pike
ID: 41860684
I'll make a dummy
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41860689
OK, Chris
0
 

Author Comment

by:chris pike
ID: 41860694
Here is the dummy file.
I wish it was more simple, but 95% of the columns have formula and only few will have text.

So the new formula should work on both style if possible/
Thanks
Chris
Dummy-Done-100-.xlsx
0
 

Author Comment

by:chris pike
ID: 41860700
As I add more trainings, I will insert a column for that training before the "formula to calculate" column.
This Worksheet will grow and grow.
0
 
LVL 22

Accepted Solution

by:
Roy Cox earned 2000 total points
ID: 41860718
I'm not sure if I understand the problem correctly. When I put =COUNTIF($C6:$AN6,"Done")/COLUMNS($C6:$AN6) into A)6 it returns 95. Done in that row is generated by formulas
Dummy-Done-100-.xlsx
0
 

Author Comment

by:chris pike
ID: 41860748
Not sure why  it is working now.
I tried the formulas and none of them worked.
Now it is working fine.
Thanks Roy
Chris
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41860751
Pleased to help
0
 

Author Comment

by:chris pike
ID: 41860755
Is there an easy way to add a column??
It is a huge pain in the butt because there is a table in the bottom part.
Wont let me just add a column.
Thanks
In the dummy file I can add a column but in my large working sheet the insert is greyed out
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41861586
If you make the upper data into a Table as well then you can add Columns independently to each Table
0
 

Author Comment

by:chris pike
ID: 41862118
Hmmm OK I will give it a try THanks
Chris
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41862537
Here's the example. You need to just right click in the upper table and choose Insert Table Column
Dummy-Done-100-.xlsx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

926 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