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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!