Average formula with error should give a blank cell
I used this formula below to give me the "DOC % Complete" but it is missing something. If the cell has “Cancelled” the Docs % Complete column should be blank but right now is giving me 50%
=IFERROR(AVG(ISDATE([Design Circuit Recommendation Actual]@row), 0.5) + IF(ISDATE([Design Velo Recommendation Actual]@row), 0.5), "")
Any help, I will appreciate it
Rob
Answers

I am not entirely sure what you want but I see several issues. Lets format the formula to make it more readable:
=IFERROR( AVG( ISDATE([Design Circuit Recommendation Actual]@row), 0.5 ) + IF( ISDATE( [Design Velo Recommendation Actual]@row ), 0.5 ), "")
The issues:
 the average function AVG() I suspect you wanted to use an IF() here.
 ISDATE() will return a True or False (1 or 0)
 for the IF() statements you don't have a false value set, so it will default to returning zero 0
 given the formula structure, you are never likely to trigger an error, so IFERROR() will never return the empty string
 I am not sure what is suppose to happen if one of your values is a Date and one is "Cancelled" so it is hard to advise the best solution

Lee
If I remove AVG
=IFERROR(IF(ISDATE([Design Circuit Recommendation Actual]@row), 0.5) + IF(ISDATE([Design Velo Recommendation Actual]@row), 0.5), "") I am getting 0% now for Cancelled but I want blank because the formula says IFERROR is not a date then " "
There is something that I am doing wrong
Rob

Lee
I think the formula needs to change to give me the result that I want, so it would be something like:
It is just the idea
IF(AVG(ISDATE(column 1), 0.5), IF(ISDATE column2), 0.5), the result will me 100% but if there is "Cancelled" in (column 1) but a date in (column 2) = 100% or vice versa, meaning if one column has a date and the other one has "cancelled" the result is 100% but if there are "Cancelled" in both columns that the result will be blank
Is there a way to create a formula with this idea?
Rob
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!