| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . . |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . . |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . . . |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . . . |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I average a group of cells and ignore a #div/0! error? | Excel Discussion (Misc queries) | |||
| ignore #NA in average formula | Excel Discussion (Misc queries) | |||
| ignore MAX and MIN values in a set to calculate average | Excel Discussion (Misc queries) | |||
| How to make average function ignore MIN and MAX | Excel Worksheet Functions | |||
| Average calcs - ignore sheets | Excel Worksheet Functions | |||
Linear Mode
