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


.



.



.