Average multiple ranges of numbers excl zeros

[vicsalt]

Help with what should be a easy solution
I will set the scene

Column named Parts Made, (31 rows 1 for each day dated for every day)
I want to average the parts made on Mon to Fri and make a projection of how many parts we should by month end.
I also want to average the parts made on weekends as its a lot smaller shift)
We will ignore the weekends as the solution will be the same

Im trying to average only the cells with values across multiple ranges it isn't a continuous column of cells if that makes sense, would upload image if i knew how

Thanks for any assistance

 

[barry houdini]

Would be nice for someone to offer advice on a solution, Ive explained the issue, all getting off track

I see that NARATANK991 offered a solution, does that not work - if not then perhaps explain why not?

 

[NARAYANK991]

Would be nice for someone to offer advice on a solution, Ive explained the issue, all getting off track

Hi ,

Please understand that help is difficult to offer because we have not understood your problem as yet.

Your first post spoke of only one column ; I have assumed that the 31 days of the month are in rows in one column ; the image you have posted is not sharp enough to reveal either the column letters or the row numbers.

There is really nothing much that anyone can do to help.

Either you post all the worksheet references that are involved , or make use of the tools in this forum to post your worksheet data in a form that others can copy and paste to a workbook , or as a last resort , you upload your file to a public file-sharing site such as DropBox.

 

[vicsalt]

Hi ,

Please understand that help is difficult to offer because we have not understood your problem as yet.

Your first post spoke of only one column ; I have assumed that the 31 days of the month are in rows in one column ; the image you have posted is not sharp enough to reveal either the column letters or the row numbers.

There is really nothing much that anyone can do to help.

Either you post all the worksheet references that are involved , or make use of the tools in this forum to post your worksheet data in a form that others can copy and paste to a workbook , or as a last resort , you upload your file to a public file-sharing site such as DropBox.

i added a link to the image so it was clear, a column is correct not a row, the image shows the detail , i couldn’t create a public folder in Dropbox it needed emails to share with, I’m spending more time establishing how to share than dealing with the issue. I’m sure my explanation is fine. But hey let’s try again

in excel create a column with 10 random numbers from cell A1 to A10. Then place 10 zeros in cells A11 to A15.

no select cells A1 to A3, A6 to A9, A12 to A5.
cell A16 needs to be the average of the selected cells excluding any zeros.

this is my problem. My solution is to put the letter”n” in each zero cell however my list of numbers are functions so I have to update the n cells every day. Ok but sure there is a better solution

 

[NARAYANK991]

Hi ,

Thanks for these details.

How will this selection be done ? Can these range references $A$1:$A$3 , $A$6:$A$9 , $A$12:$A$15 be hard-code into the formula , or will there be some other mechanism for specifying these range references ?

 

[NARAYANK991]

Hi ,

See if this array formula , to be entered using CTRL SHIFT ENTER , works :

=AVERAGE(IF(N(OFFSET($A$1,{0,1,2,5,6,7,8,11,12,13,14},))<>0,N(OFFSET($A$1,{0,1,2,5,6,7,8,11,12,13,14},))))

The numbers within the OFFSET function are for specifying the cell references you have mentioned.

 

[Aladin Akyurek]

Hi ,

Thanks for these details.

How will this selection be done ? Can these range references $A$1:$A$3 , $A$6:$A$9 , $A$12:$A$15 be hard-code into the formula , or will there be some other mechanism for specifying these range references ?

Try...

=SUM($A$1:$A$3 , $A$6:$A$9 , $A$12:$A$15)/INDEX(FREQUENCY(($A$1:$A$3 , $A$6:$A$9 , $A$12:$A$15),0),2)

Here how to post the data...

A1:A3

5
0
0

A6:A9

5
4
empty

A12:A15

4
6
2
6

followed by the expected result.