Average lookup

Hi All,

I'm new to this sort of thing, so your help is appreciated.

I need to calculate the average number of days a report was open based on the criteria Month & Customer and display the result in a seperate worksheet.

For example, I would enter customer A in a cell in worksheet 2 and the formula would complete the average number of days a report was open below the corresponding months.

Everything I have tried comes up with an error !?! Help......

Hello Beef,

You can use this formula in B2 copied across

=LOOKUP(10^10,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!$C2:$C13=$A2,IF(Sheet1!$B2:$B13-DAY(Sheet1!$B2:$B13)+1=B$1,Sheet1!$D2:$D13)))))

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar, see attached

See attached.
Don't enter customer name in Column A.
Names will be added, when you enter a new name on sheet1.
modytrane

Thanks daddylonglegs thats works perfect.

You have saved me a lot of time.

Beef

Thanks also modytrane, your solution although different works also.

Thankyou

Beef