Solved

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

Posted on 2016-10-26
16
27 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 17

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 17

Expert Comment

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

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 17

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 17

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 17

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 17

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 17

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 17

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now