How to skip if no true condition found in IFS statement

If all of the conditions in the IFS statement are false I would like it to return no value. This is because the values returned are within an AVERAGE function and if no value is returned I would like it to simply not be factored in the assessment.

As it is, if all the conditions are false it returns N/A ruining the entire function.

Can encapsulate your entire formula with IFERROR. Second parameter will allow you to have whatever you want instead of the hashtag error. If you want it to be blank, a formula would look like below. You can change the "" to whatever you want it to say.

Please Login or Register  to view this content.

The point is that I want the average to be calculated, I just want it to not be part of the average. Since it not being included reduces the total number of values this has little impact.

I meant to say skip the value, not the entire average, so that the average itself can be computed.

I am not following you. What is the current formula you are using?

I think this will do it:

Please Login or Register  to view this content.

There is probably something more elegant out there, but that should get the job done.

I know pretty well how averages work. Please note that nowhere in that formula is AVERAGE used The denominator is determined by the second SUM formula, so it will return a number from 1 to 3. Try it out and see

Thank you very much for pointing out that oversight. The issue is that it would not automatically calculate the value to divide it by, unless you can count the number of 0s calculated within the function.

Originally Posted by reagr

Thank you very much for pointing out that oversight. The issue is that it would not automatically calculate the value to divide it by, unless you can count the number of 0s calculated within the function.

You should not need anything else. I will explain how that works. Average = sum of values / number of values i.e. (2+3+5+7) / 4. That formula uses two SUM formulas. The first one will sum up the values returned by all your IFS formulas. They include your decimals. The second SUM formula, after the / symbol, will result in a 1 or a 0 based on your values of Approve, Disapprove, or Skipped. The maximum value there is 3. It could possibly error out if you choose skip for all values. That second sum, in essence, will count the number of values.

Hope that makes sense at all.

I obviously didn't read your function nearly carefully enough--I skimmed over it because of its length. Thank you for the explanation.

I have attached a workbook that will do what you want and is much cleaner to look at. Hope it helps.