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.
