I have a set of data like that shown below. I am trying to do an average ifs for this data to average the number of trips, the amount of vehicles must be bigger than 0 AND the number of vehicles must equal the amount of people. what is the formula in order to do this?

I am able to write the formula for if greater than zero, but I am having difficulty finding out how to say that the value in one column (vehicles) must equal the one in the next (people)

Does it work? Sum/Count

``Please Login or Register  to view this content.``

No I am doing a cross classification analysis so I need to be able to use averageifs

Try

{=average(if((b2:b12>0)*(b2:b12=c2:c12),a2:a12))}

Unfortunately this didnt work either

Originally Posted by ventura7
No I am doing a cross classification analysis so I need to be able to use averageifs
SUMPRODUCT (Sum)/SUMPRODUCT(Count) actually works like average(ifs)
Pls add my formula in #2 and manual outcome input, to see if it works.

It is throwing out an error

Sure this should be so hard? Why can I not just use averageifs(A1:A12, B1:B12, ">0", C1:C12, ">"&B1:B12)

Can someone explain why this does not work?

Your sample file isn't great. Why?

1. There are NO rows where vehicles = people.

2. There are NO manually calculated expected results.

So, we have to play guessing games as to what you actually want. I don't much like guessing games. Please amend and update your smple file.

There are currently 1 users browsing this thread. (0 members and 1 guests)