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

Find more posts tagged with

Formulas

functionality

IT & Operations

Project Management

Accepted answers

All comments

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:

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.