MIN & AVERAGE ignore 0

[Mike]

I am trying to get min & average 0n cells
G5,K5,O5,S5W5,AA5,AE5,AI5,AM5,AQ5,AU5,AY5,BC5,BG5,BK5,BO5,BS5,BW5,CA5,CE5,CI5,CM5,CQ5,CU5,CY5,DC5,DG5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5,FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC5,
all these cells have zero,s until data is entered on seperate work sheets
these cells im trying to min or average = 52 weeks so i want the min &
average to be calculated as each weeks data is entered can anyone please help
I TRIED ALOT OF DIFFERENT FORMULAS BUT I CAN,T GET IT TO WORK

 

[Trevor Shuttleworth]

Mike

For the average, it might be useful to have a range of "helper" cells to aid
the calculation. This wouldn't necessarily replace the existing cells.

For example, if you used the range A5 to BZ5, the (average) formula would
be:

=IF(ISERROR(SUM(A5:BZ5)/COUNTIF(A5:BZ5,"<>")),0,SUM(A5:BZ5)/COUNTIF(A5:BZ5,">0"))

Regards

Trevor

 

[Gary''s Student]

First create a reference table in an un-used column:

In A1 thru A.....

=IF(G5=0,"",G5)
=IF(K5=0,"",K5)

and so on down the column


Then use:
=AVERAGE(A1:A100)
and:
=MIN(A1:A100)

Making the zeros into blanks allows AVERAGE and MIN to ignore them.

 

[T. Valko]

Are there any negative numbers in the range?

Are there any numeric values in the cells between the target cells?

G5...H5...I5...J5...K5
10.....?.....?.....?....20

What's in H5:J5 ?

 

[Mike]

Cells in between cells that i am targetting have zero values as well i want
to get min & average for these cells example efficiecy is G5,uptime is H5,
QUALITY IS I5, & OEE is J5 ......ETC.

 

[Mike]

NO the numbers will not be negative

Are there any negative numbers in the range?

Are there any numeric values in the cells between the target cells?

G5...H5...I5...J5...K5
10.....?.....?.....?....20

What's in H5:J5 ?

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

Try these array formulas** :

Average:

=AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))

Min:

=MIN(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Mike]

This works great but on average colum i get DIV/0 can i eliminate this

 

[T. Valko]

This works great but on average colum i

get DIV/0 can i eliminate this

That would mean you're trying to divide by 0. In other words, none of the
cells in the range meet the conditions.

What version of Excel are you using?

What result do you want to replace the error?

 

[Mike]

i am using 2007 & i would like to replace DIV/0 with zero to make it look
cleaner if possible

 

[T. Valko]

i am using 2007

Ok, that makes it a lot easier.

=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)

Still array entered!

 

[Mike]

Thanks you were a great help

 

[T. Valko]

You're welcome. Thanks for the feedback!