[Solved] Averaging multiple non-consecutive cells

Hello!

I am new to this forum, but have been using Excel for some time now. I came across a scenario today however which stumped me, and was hoping that perhaps someone on here might have a solution!

I am attempting to get an average of multiple non consecutive cells, ignoring anything that is equal to '0'. Each of my cells is 10 cells apart. I've tried a few different formulas, but none of them have worked yet. I have well over 100 cells total that I'm trying to apply this formula too, screencap below for reference!

Excel Snippet.png

These are the formulas I've tried:

=Average(B9,B20,B31,B64,B75,B86,B97,B108,B141,B152,B163,B174,B185,B218,B229,B240,B251,B262,B295,B306)

^doesn't take out zeros

=SUM(Log_On_Bradley)/COUNT(Log_On_Bradley)

^Same as above (Where "Log_On_Bradley" is just a named range referencing the above cells)

=SUM(Log_On_Bradley)/COUNTIF(Log_On_Bradley, ">0")

^Throws an error

=AVERAGEIF(B8,B19,B30,B63,B74,B85,B96,B107,B140,B151,B162,B173,B184,B217,B228,B239,B250,B261,B294,B305, ">0")

^Throws an error for too many arguments

Thanks in advance for any help!

Leah

Maybe?

Please Login or Register  to view this content.

Ah..yes sorry about that.
Maybe search for , "Average every nth row"

Hi -

Are you really trying to average every nth cell or are you trying to average each employee's login time that is not zero?

Are all of these "tables" on the same worksheet? You should have attached a sample. We cannot even see the column and row headers in the pics.
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

sol'n deleted....

If everything is on one sheet, maybe:
=SUMPRODUCT(--(A:A=z1),(B:B))/SUMPRODUCT(--(A:A=z1),--(B:B>0))
z1=address of first name in list of names in the red box.

For averaging non-consecutive cells ignoring zeroes you can use this type of formula:

=SUM(B3:B7,D3:D7,F3:F7)/INDEX(FREQUENCY((B3:B7,D3:D7,F3:F7),0),2)

see explanation here