Solved

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

Posted on 2016-10-26
16
32 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 18

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 18

Expert Comment

by:Roy_Cox
ID: 41860645
I meant using all formulas involved with the Text Function.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 18

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 18

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 18

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 18

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 18

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 18

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

837 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