IF ERROR help for a nested IF AVG formula

Hi there! I have an IF followed by AVG formula that is working ok- looks at multiple columns and averages them (frankly it is problematic, because it returns an average even when not all 6 columns are filled out, help? Is there a way for it to only calculate the average once all 6 columns have a value added? Honestly if this part of the problem persists, I think it will still be ok).

When there are no values added to any of those 6 columns, the formula returns the #DIVIDE BY ZERO error. I know I should be able to utilize an IFERROR function at the beginning of my formula to change that error message to either a " " or "Data Needed" return but I'm stuck. This is the current formula:

=IF([SALES HELPER COLUMN]@row = "SALES", AVG([P1 Numeric Score]@row, [P2 Numeric Score]@row, [P3 Numeric Score]@row, [P4 Numeric Score]@row, [P5 Numeric Score]@row, [P6 Numeric Score]@row))

Any thoughts? I'm newer to writing out more complex/nested/multi-function formulas.

Thanks!

Find more posts tagged with

Formulas

IF(

Accepted answers

All comments

NickStaffordPM

So you could try this, try to make each of those six values part of the intial logical test

=IF(AND([P1 Numeric Score]@row>1, [P2 Numeric Score]@row >1, [P3 Numeric Score]@row > 1, [P4 Numeric Score]@row >1 , [P5 Numeric Score]@row > 1, [P6 Numeric Score]@row > 1,([SALES HELPER COLUMN]@row = "SALES")), AVG([P1 Numeric Score]@row, [P2 Numeric Score]@row, [P3 Numeric Score]@row, [P4 Numeric Score]@row, [P5 Numeric Score]@row, [P6 Numeric Score]@row),"All Values Not Inputted")

It essentially is going to look to be sure all those 6 values are greater than 1 (IE not blank) AND that the Sales helper column is = to "SALES". If all values are greater than one and SALES is present, it will average the values, if not, the text "All Values Not Inputted" will populate. To put an if error on the front, you could just do the following:

=IFERROR(IF(AND([P1 Numeric Score]@row>1, [P2 Numeric Score]@row >1, [P3 Numeric Score]@row > 1, [P4 Numeric Score]@row >1 , [P5 Numeric Score]@row > 1, [P6 Numeric Score]@row > 1,([SALES HELPER COLUMN]@row = "SALES")), AVG([P1 Numeric Score]@row, [P2 Numeric Score]@row, [P3 Numeric Score]@row, [P4 Numeric Score]@row, [P5 Numeric Score]@row, [P6 Numeric Score]@row),"All Values Not Inputted"),"")

Let me know if this does not help!

SoS | Dan Palenchar

Hello @SoTho,

You can use this formula

=IF(AND([Sales Helper]@row = "SALES", COUNT([P1 Numeric Score]@row:[P6 Numeric Score]@row) = 6), AVG([P1 Numeric Score]@row:[P6 Numeric Score]@row))

Note that this assumes the 6 P# Numeric Score columns are next to each other.

Here is a published Sheet you can peruse.

Hope that helps!

SoTho

Thank you so much Dan! My columns are separated but I can adjust and try this!

SoTho

Thanks also Nick! Excited to try both of these out!

SoTho

hmmm i had issues with both of these….

Dan, i got an error about the syntax when i tried to plug yours in and Nick, yours seemed to work but then I noticed that it wasn't populating an average for several rows that DID have all 6 columns filled out.

Thanks anyways community! I'll keep playing around….

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.