**Question description:**

*This user has given permission to use the problem statement for this
blog.*

I just need to edit this formula, {=IFERROR(AVERAGEIFS(B6:D6,F6:H6,"FALSE"),AVERAGEIF(B6:D6,"<>0"))}, to return "-" or "0" if there is nothing in column D. I can share the entire worksheet if need be.

Solved by V. A. in 47 mins

This is the chat thread from the real Excelchat help session. It contains no private user
information.

User
13/08/2018 - 05:33

Any questions?

Excelchat Expert
13/08/2018 - 05:33

Hello

Excelchat Expert
13/08/2018 - 05:33

Welcome to got it pro!

Excelchat Expert
13/08/2018 - 05:33

I saw you have a question regarding the formual

Excelchat Expert
13/08/2018 - 05:33

Please allow me to take a look

Excelchat Expert
13/08/2018 - 05:36

=IFERROR(or(AVERAGEIFS(B5:D5,F5:H5,"FALSE"),AVERAGEIF(B5:D5,"<>0")),0)

Excelchat Expert
13/08/2018 - 05:36

This should works and return 0

User
13/08/2018 - 05:36

Good deal! Let me do a quick check

Excelchat Expert
13/08/2018 - 05:36

No problem!

User
13/08/2018 - 05:37

Almost there, it still needs to Average B:D

Excelchat Expert
13/08/2018 - 05:38

Not quite sure what you mean

Excelchat Expert
13/08/2018 - 05:38

You mean =IFERROR(AND(AVERAGEIFS(B5:D5,F5:H5,"FALSE"),AVERAGEIF(B5:D5,"<>0")),0)

User
13/08/2018 - 05:38

When I copied into my data set, the formula returned either "0" or True

Excelchat Expert
13/08/2018 - 05:39

Ok

Excelchat Expert
13/08/2018 - 05:40

Is that correct?

User
13/08/2018 - 05:41

No, it needs to return the average of the three unless there is no result in D. The existing Formula I sent also removes the major outliers from the 3. That feature also needs to stay

Excelchat Expert
13/08/2018 - 05:44

=if(IFERROR(OR(AVERAGEIFS(B5:D5,F5:H5,"FALSE"),AVERAGEIF(B5:D5,"<>0")),0)="0",0,"TRUE")

Excelchat Expert
13/08/2018 - 05:44

Maybe this is a better one in your case?

User
13/08/2018 - 05:46

No, it only returns TRUE now

Excelchat Expert
13/08/2018 - 05:46

#DIV/0!

Excelchat Expert
13/08/2018 - 05:46

=if(IFERROR(OR(AVERAGEIFS(B5:D5,F5:H5,"FALSE"),AVERAGEIF(B5:D5,"<>0")),0)="0",0,OR(AVERAGEIFS(B5:D5,F5:H5,"FALSE"),AVERAGEIF(B5:D5,"<>0")))

User
13/08/2018 - 05:49

Nope... Now it's returning only TRUE or #DIV/0!

Excelchat Expert
13/08/2018 - 05:50

Please tell me what formula you are trying to create

User
13/08/2018 - 05:51

The formula I provided compares the last three invoices of a contract and averages them if there are no major outliers (it removes one of the 3 invoices if it is 50

User
13/08/2018 - 05:52

50% larger or smaller than the others

Excelchat Expert
13/08/2018 - 05:52

Ok

User
13/08/2018 - 05:52

I'm looking to have the formula work the exact same way except not calculate if the last invoice is blank

User
13/08/2018 - 05:52

the last invoice would be in column D

Excelchat Expert
13/08/2018 - 05:54

If there is a blank, then you should use <>""

Excelchat Expert
13/08/2018 - 05:54

That will capture the blank

User
13/08/2018 - 05:55

Yup, but I don't know how to insert that into a IFERROR

Excelchat Expert
13/08/2018 - 05:55

Well, the iferror only allow 2 parameters

Excelchat Expert
13/08/2018 - 05:55

if 1st one doesn't match then evaluate then 2nd sceanrio

Excelchat Expert
13/08/2018 - 05:56

If there are extra condition, I think nested if statement is more flexible

User
13/08/2018 - 05:58

Any chance you could help with that? I can share the actual file to provide more context

Excelchat Expert
13/08/2018 - 05:59

That should be fine

User
13/08/2018 - 06:00

The sheet I'm working off of is Igor Pivot

[Uploaded an Excel file]

Excelchat Expert
13/08/2018 - 06:00

ok

Excelchat Expert
13/08/2018 - 06:00

I think the issue is more focus on formual

User
13/08/2018 - 06:00

[Uploaded an Excel file]

User
13/08/2018 - 06:00

Sorry, V3 is the most updated

Excelchat Expert
13/08/2018 - 06:01

ok

Excelchat Expert
13/08/2018 - 06:03

0

Excelchat Expert
13/08/2018 - 06:03

=IFERROR(IFERROR(AVERAGEIFS(B6:D6,F6:H6,"FALSE"),AVERAGEIF(B6:D6,"<>0")),0)

User
13/08/2018 - 06:05

Almost! It's still calculating if D is blank. We need it to return a 0 if D is blank

Excelchat Expert
13/08/2018 - 06:06

Right now D is blank

Excelchat Expert
13/08/2018 - 06:06

correct?

Excelchat Expert
13/08/2018 - 06:07

=if(D6="",0,IFERROR(IFERROR(AVERAGEIFS(B6:D6,F6:H6,"FALSE"),AVERAGEIF(B6:D6,"<>0")),0))

Excelchat Expert
13/08/2018 - 06:07

You want D6 to be blank correct?

User
13/08/2018 - 06:08

Yah, I see it working there but for some reason it's not working on my excel

Excelchat Expert
13/08/2018 - 06:08

hmmmm

Excelchat Expert
13/08/2018 - 06:08

It should be the right formula

Excelchat Expert
13/08/2018 - 06:08

Please try to restart the file

Excelchat Expert
13/08/2018 - 06:08

And it should work.....

User
13/08/2018 - 06:09

Doing that now. Mind dropping the formula in the last file I shared?

Excelchat Expert
13/08/2018 - 06:09

You can copy this formula into somewhere in your local computer

User
13/08/2018 - 06:11

I think my Check in column F is no longer working

Excelchat Expert
13/08/2018 - 06:11

Ok

Excelchat Expert
13/08/2018 - 06:12

That might be another issue

Excelchat Expert
13/08/2018 - 06:12

But regarding this formula it should be correct at this point

User
13/08/2018 - 06:12

Great, thanks!

Excelchat Expert
13/08/2018 - 06:12

Please feel free to let me know if you have any questions!

Excelchat Expert
13/08/2018 - 06:12

Also, please feel free to leave me some positive feedback at the end of this session

Excelchat Expert
13/08/2018 - 06:12

You have a great day!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user
information.*