MIN & AVERAGE ignore 0
 

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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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

MIN & AVERAGE ignore 0
 

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

"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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

MIN & AVERAGE ignore 0
 

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.
--
Gary''s Student - gsnu200908


"Mike" wrote:

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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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

MIN & AVERAGE ignore 0
 

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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

MIN & AVERAGE ignore 0
 

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.

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.

MIN & AVERAGE ignore 0
 

NO the numbers will not be negative

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.

MIN & AVERAGE ignore 0
 

Try these array formulas** :

Average:

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

Min:

=MIN(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC50,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.

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
NO the numbers will not be negative

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.

MIN & AVERAGE ignore 0
 

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

"T. Valko" wrote:

Try these array formulas** :

Average:

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

Min:

=MIN(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC50,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.

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
NO the numbers will not be negative

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.



.

MIN & AVERAGE ignore 0
 

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?

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
This works great but on average colum i get DIV/0 can i eliminate this

"T. Valko" wrote:

Try these array formulas** :

Average:

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

Min:

=MIN(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC50,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.

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
NO the numbers will not be negative

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.



.

MIN & AVERAGE ignore 0
 

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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
This works great but on average colum i get DIV/0 can i eliminate this

"T. Valko" wrote:

Try these array formulas** :

Average:

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

Min:

=MIN(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC50,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.

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
NO the numbers will not be negative

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.



.



.

MIN & AVERAGE ignore 0
 

i am using 2007

Ok, that makes it a lot easier.

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

Still array entered!

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
i am using 2007 & i would like to replace DIV/0 with zero to make it look
cleaner if possible

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
This works great but on average colum i get DIV/0 can i eliminate this

"T. Valko" wrote:

Try these array formulas** :

Average:

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

Min:

=MIN(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC50,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.

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
NO the numbers will not be negative

"T. Valko" wrote:

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


"Mike" wrote in message
...
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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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


.



.



.

MIN & AVERAGE ignore 0
 

Thanks you were a great help

"Mike" wrote:

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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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

MIN & AVERAGE ignore 0
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
Thanks you were a great help

"Mike" wrote:

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,DG 5,DK5,DO5,DS5,DW5,EA5,EE5,EI5,EM5,EQ5,EU5,EY5,FC5, FG5,FK5,FO5,FS5,FW5,GA5,GE5,GI5,GM5,GQ5,GU5,GY5,HC 5,
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