asked on

Average IF

Hi,

Im getting a #DIV/0 on the below formula

=AVERAGEIFS(WinLoss!R:R,WinLoss!BC:BC,"Yes",WinLoss!BA:BA,"Yes",WinLoss!F:F,"<>Renew",WinLoss!W:W,"Lost",WinLoss!W:W,"Cancelled")

Can anyone see why?

Thanks

Rgonzo1971

Hi,

have you tested what the Help says

If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.
If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.

Regards

Rory Archibald

WinLoss!W:W,"Lost",WinLoss!W:W,"Cancelled"

Those last two can't both be true at once.

Seamus2626

ASKER

They can be true, in terms of the data, we can have cancelled and lost, which is what im looking for, must the structure of the IF change?

Thanks

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

I get it now!

Out of interest, why cant entire column reference be used?

Rory Archibald

Because it will make the formula calculate really slowly.

Rory Archibald

BTW, you could also use:

=SUM(SUMIFS(WinLoss!R:R,WinLoss!BC:BC,"Yes",WinLoss!BA:BA,"Yes",WinLoss!F:F,"<>Renew",WinLoss!W:W,{"Lost","Cancelled"}))/SUM(COUNTIFS(WinLoss!BC:BC,"Yes",WinLoss!BA:BA,"Yes",WinLoss!F:F,"<>Renew",WinLoss!W:W,{"Lost","Cancelled"}))

Seamus2626

ASKER

That all makes sense, thanks Rory!