Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula Help
Hello,
Can someone please help me with this formula. I don't know what I'm doing wrong. I have 3 auto fail items and it seems that only the PCI one works. If either Solved, Protected, or PCI are scored No, the end score needs to be 0%. Everything is either scored, Yes, No, or Not Applicable.
I have 8 columns:
A, B, C, D, E, Solved, Protected, PCI
I'm using:
=IF([Solved]1 = "No", 0) + IF([Protected]1 = "No", 0) + IF([PCI]1 = "No", 0), (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))
Thank you,
Barb
Comments
-
Barb,
Your formula is imbalanced (has non matching counts of "(" and ")"
I assume your formula is actually located in a 9th column (otherwise the reference to [A]1:[PCI]1 would lead to a circular reference.
Breaking it down:
=IF([Solved]1 = "No", 0)
results in 0 when Solved is "No" but gives a blank when it is not "No".
Same for the next two parts
+ IF([Protected]1 = "No", 0)
+ IF([PCI]1 = "No", 0)
And then that comma looks out of place
, (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))
and the last two ")" are what is causing the imbalance.
.....
So, assuming A,B,C,D,E hold values,
if ANY of the other three are "No" then 0
otherwise, count the "Yes" and "Not Applicables" - equally weighted.
Let's start with the first part, this
=IF(OR(Solved1 = "No", Protected1 = "No", PCI1 = "No"), 0, "do something")
will check for any "No" in those three columns and return 0 if it finds one (or more)
Otherwise it will print "do something"
Replace the "do something" with what you want like
5 - COUNTIF(A1:E1,"No")
will assume that any non-No is a valid input (including blanks) and since there are 5 columns just subtract the number we don't want to count.
I'm really not clear one what you are trying to do but maybe this will help you figure it out.
Craig
-
Craig you saved my day! I tried the below and it worked.
=IF(OR([Solved]1 = "No", [Protected]1 = "No", [PCI]1 = "No"), 0, (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))
Appreciate your quick response!
Barb
-
I do that a lot, mostly at night dressed as a superhero(tm).
Sometimes here too.
Glad I can help.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives