ANSWERSIF Need to find average of one column with two connected criteria. Desperate for an answer.

[Ventura7]

So I have a column with number of trips, another with number of vehicles and another with number of people.

I need to find the average of the number of trips WHEN number of vehicles is greater than 0 BUT number of workers is greater than the number of vehicles.

I have been using averageifs for this. Do anyone have any ideas what the code might look like?

I have tried =ANSWERIFS(A:A, B:B, ">0", C:C, ">"&B:B) but I am not having any luck.

Please could someone help?

V7

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:

=AVERAGEIFS(A:A, B:B, ">"&0, C:C, ">"&B:B)

 

[Ventura7]

Thank you! And unfortunately this gives me the same answer as I was getting previously. I have manually checked it and it isn't correct.

 

[alz]

Thank you! And unfortunately this gives me the same answer as I was getting previously. I have manually checked it and it isn't correct.

=AVERAGEIFS(A:A, B:B, ">"&0, C:C, ">"&B)

How about this?

 

[maabadi]

Please upload Example file & desired Result with XL2BB ADDIN (Preferable) or upload your file at free hosting site e.g. www.dropbox.com, GoogleDrive or Onedrive & input link here.
to See what is problem?

 

[sandy666]

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

 

[Ventura7]

=AVERAGEIFS(A:A, B:B, ">"&0, C:C, ">"&B)

How about this?

Unforuntately this also didnt work and gave same result as previous

 

[Ventura7]

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

Thanks for the link, I have previoulsy read through this and I understand the syntax but having difficulty with this due to the limits instead of whole values and a row in one column must be same as row in another

 

[Ventura7]

Example Book (1).xlsx

drive.google.com

This is where the file is, the values in yellow are the desired values however, I would like to be able to use averageifs to get the correct answer, the sumifs way seems sloppy

 

[maabadi]

What about this:

=SUMPRODUCT((A:A)*(B:B>0)*(C:C>B:B))/SUMPRODUCT(--(C:C>B:B))