Average rows discarding Zero

[prravich]

Hi all,

I have a simple query and I am looking for a way to achieve it in a efficient manner. I have three columns marks 1 , marks 2 and marks 3 for students. I want to calculate the average of all three marks but if any of the marks says 0 , I need to ignore that and calculate average using only other two. Any help ?

Student	Marks1	Marks 2	Marks 3
A	0	50	100
B	40	60	50

Expected Answer:

student	avg_marks
A	75
B	50

[RodL · Accepted solution]

Transpose data with Student as the Key field.

Use Summarize tool, group by Student and get Average - Ignore 0's (under Numeric)