Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

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

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
chris pike
Asked:
chris pike
  • 8
  • 8
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
Have you tried using the TEXT function  with the other formulas?
0
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
I meant using all formulas involved with the Text Function.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
chris pikeAuthor Commented:
No I have not, I don't know how to do that.
Any help is appreciated.
Thanks
Chris
0
 
Roy CoxGroup Finance ManagerCommented:
It's just an idea but can you attach an example of your workbook.
0
 
chris pikeAuthor Commented:
I'll make a dummy
0
 
Roy CoxGroup Finance ManagerCommented:
OK, Chris
0
 
chris pikeAuthor Commented:
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
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
If you make the upper data into a Table as well then you can add Columns independently to each Table
0
 
chris pikeAuthor Commented:
Hmmm OK I will give it a try THanks
Chris
0
 
Roy CoxGroup Finance ManagerCommented:
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

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!

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now