Average formula with error should give a blank cell

RobNY2

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

Lee Joramo

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:

1. the average function AVG() I suspect you wanted to use an IF() here.
2. ISDATE() will return a True or False (1 or 0)
3. for the IF() statements you don't have a false value set, so it will default to returning zero 0
4. given the formula structure, you are never likely to trigger an error, so IFERROR() will never return the empty string
5. 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

RobNY2

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

RobNY2

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