Conditional Min, Max and average - ignore "0" in non-adjacent cells

Hey,

I am trying to design several spreadsheets that display the minimum, maximum and average values of non adjacent cells. My google searches have led to some several clunky solutions that I am not sure that will work for me. I use excel to create tables created from raw data I enter. It is very difficult to make the data contiguous.

For example, this is the formula I use in on of them

=MIN(G4,G9,G14,G19,G24,G29)

Sometimes data gets entered as "0" in the cells, but its not pertinent, and I want excel to ignore it. I also would use the same cells for MAX and AVERAGE.

Is there any simple way to do this? I use a formula of this type for SEVERAL different spreadsheets and different data sets.

Thanks in advance.

Try this ...

=MIN(IF((MOD(ROW($G$4:$G$29),5)=4)*($G$4:$G$29<>0),$G$4:$G$29))

Enter with Ctrl+Shift+Enter

Change MIN to MAX or AVERAGE

Hey - that worked! Thanks! I will try and see if I can get this to work in the several other areas of the workbook under similar applications/conditions.

Thanks a TON. I cannot explain how much this is going revolutionize my workflow!