Average Formula

[pphg]

Hi Guys,

Im usually using this array formula to get average for a certain range excluding "O" value.

Please Login or Register  to view this content.

However, how can I apply it if the range is not in our straight row.

For example: =AVERAGE(V6,V10,V14,V18,V22,V26,V30,V34,V38,V42,V46,V50) ??? Some of the cells there have O value and need to exclude them.


Thanks

[benishiryo]

hi pphg, something like this?

=AVERAGE(IF(V6:V50<>0,V6:V50))

this is also an array. so press CTRL + SHIFT + ENTER

[pphg]

Hi Benishiryo

It is the same as the first formula I posted, kindly note that they are not in one straight now, they skip, V6, V10, V14, V18... I need an average formula that will only read those specified cells, and exclude if one of them have "0" value.

Thanks

[benishiryo]

i see. i was assuming those unmentioned ones are without values. try this array then:
=AVERAGE(IF(MOD(ROW(V6:V50),4)=2,IF(V6:V50<>0,V6:V50)))

could you upload a sample Excel file if it doesnt work. it's also always good to upload one to illustrate better & with the desired results shown

[pphg]

Hi Benishiryo

Thanks for your reply.

Attached if the sample file with detailed desired outcome included in the sheet. Thanks!

[:) Sixthsense :)]

In G2 cell - Array Formula Requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

Drag the formula down.

[benishiryo]

i would go with Sixthsense's formula now that you've uploaded the file. but my earlier solution should have worked for you as well, according to what you described. i've uploaded the file for your reference