# Excel - IF Function Problem - Expert Solution

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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.