Combining Average and Vlookup formula

[djw1998]

I'm looking to average specific line items across a couple different sheets, but only want to include numbers that are greater than 0.

For Example - I want to average rent revenue across a few different tabs but dont want the tabs with $0 to bring down the average.

My current formula is: =AVERAGE(VLOOKUP(B7,Sheet1!B6:Q102,16,FALSE),VLOOKUP(!B7,'Sheet2'!B6:Y95,24,FALSE),VLOOKUP(B7,Sheet3!B7:Z97,25,FALSE))

 

[Fluff]

How about

=LET(a,VLOOKUP(B7,Sheet1!B6:Q102,16,FALSE),b,VLOOKUP(B7,Sheet2!B6:Y95,24,FALSE),c,VLOOKUP(B7,Sheet3!B7:Z97,25,FALSE),AVERAGE(IF(a=0,"",a),IF(b=0,"",b),IF(c=0,"",c)))

 

[djw1998]

How about

=LET(a,VLOOKUP(B7,Sheet1!B6:Q102,16,FALSE),b,VLOOKUP(B7,Sheet2!B6:Y95,24,FALSE),c,VLOOKUP(B7,Sheet3!B7:Z97,25,FALSE),AVERAGE(IF(a=0,"",a),IF(b=0,"",b),IF(c=0,"",c)))

My only issue is that it seems I get an error message when one of the sheets has a certain line item that the others do not.

For Example: "Model Apartment" returns a #VALUE! error.

 

[Fluff]

Do you have #value errors on any of the other sheets?

 

[djw1998]

Do you have #value errors on any of the other sheets?

I'm just using this formula on the main tab, so I haven't looked on other sheets.

 

[Fluff]

But the formula is looking at the other sheets.

 

[djw1998]

But the formula is looking at the other sheets.

This formula is being used on one master sheet to pull averages from 3 other sheets. If one of the other sheets does not have a line item that is listed on the master sheet, it will return an error message.

 

[Fluff]

How about

=LET(a,XLOOKUP(B7,Sheet1!B6:B102,Sheet1!Q6:Q102,""),b,XLOOKUP(B7,Sheet2!B6:B95,Sheet2!Y6:Y95,""),c,XLOOKUP(B7,Sheet3!B7:B97,Sheet3!Z7:Z97,""),v,VSTACK(a,b,c),AVERAGE(FILTER(v,v<>"")))