Average data in multiple columns based on column header, row criteria and value

[devious66]

I need help getting an average of column data. I only want to average values which are greater than zero (or not blank), based on column header data, and row criteria. Here is an example of what I need.

WEEK1 WEEK2 WEEK3 WEEK4
ITEMA 50 0 30 50
ITEMB 100 125 125 50
ITEMC 0 0 40 30


So if I wanted average data for ITEMA, only for weeks 2-4, and only if greater than zero, the result would be 40.

Thanks so much for anyone who can help.

devious66

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​		WEEK1	WEEK2	WEEK3	WEEK4		average
2​	ITEMA	50​	0​	30​	50​		40​
3​	ITEMB	100​	125​	125​	50​
4​	ITEMC	0​	0​	40​	30​

<tbody>
</tbody>

G2=AVERAGE(IF(A2:A4=A2,IF(RIGHT(B1:E1,1)+0>=2,IF(RIGHT(B1:E1,1)+0<=4,IF(B2:E4<>0,B2:E4))))) control+shift+enter

 

[devious66]

Marzio,

Thanks for your reply, and I apologize. My first time posting here, and I realize I needed to be much more specific. The "week" columns will actually be week ending dates, and I want to average the data in a specific date range (out of 52 week columns), but only where the entries within that range are greater than zero.

Sorry for the effort you put into your answer. I will try to be better in later posts!

Thanks,

Devious66