Solved

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

Posted on 2016-10-26
16
37 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
[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
  • 8
  • 8
16 Comments
 
LVL 19

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 19

Expert Comment

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

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 19

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 19

Accepted Solution

by:
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

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 19

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 19

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 19

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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.

752 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