Solved

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

Posted on 2016-10-26
37 Views
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
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
• 8
• 8

LVL 19

Expert Comment

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

Author Comment

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 19

Expert Comment

ID: 41860645
I meant using all formulas involved with the Text Function.
0

Author Comment

ID: 41860658
No I have not, I don't know how to do that.
Any help is appreciated.
Thanks
Chris
0

LVL 19

Expert Comment

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

Author Comment

ID: 41860684
I'll make a dummy
0

LVL 19

Expert Comment

ID: 41860689
OK, Chris
0

Author Comment

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

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 19

Accepted Solution

Roy_Cox earned 500 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

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 19

Expert Comment

ID: 41860751
0

Author Comment

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 19

Expert Comment

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

ID: 41862118
Hmmm OK I will give it a try THanks
Chris
0

LVL 19

Expert Comment

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

Question has a verified solution.

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

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,â€¦
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month2 days, 15 hours left to enroll