Getting average of subtotals only if value of individual cells are greater than zero

Try this ARRAY formula.

Formula:

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

Looks strange..In this case:

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi,

Several things:

Firstly, the SUBTOTAL formula, since it is using column A as a reference, and since this column is textual, using 2 for the function_num parameter will not work: either use 3 or select a different column (e.g. containing numerical data only) to use as its array reference.

Secondly, I am not sure what the ">0" is supposed to achieve in this construction.

Thirdly, the starting reference for the OFFSET formula (A1 in this case) needs to be fixed relative to the column so that when dragged across to the right, it remains so (otherwise you may encounter similar issues with the SUBTOTAL function as explained in my first point).

Fourthly, it does not appear that this formula was committed as an array formula when I opened your attachment.

All in all, your formula in column O should be:

=AVERAGE(IF(SUBTOTAL(3,OFFSET($A1,ROW($A$2:$A$217)-ROW($A$1),0,1)),IF(O2:O217>0,O2:O217)))

Regards

Note also that your desired total in column P of 21 is incorrect, since this average is derived from an array including the zero in row 69, which contradicts the condition in the formula of excluding values not greater than 0.

Regards

No!

The AVERAGE function is doing the averaging! The SUBTOTAL function is used as it is compatible with evaluating arrays containing hidden/filtered rows - the technique in this construction may require some explanation, which I will try later if you really want.

Are you sure you know how to enter array formulas? Are you sure that you're entering it in a workbook which is identical to that which you posted?

Regards

I don't see any bad attitude! I see a man who tries-hard- to helps you! Pls..!

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

Asking someone whether they are 'sure' that they have understood and followed instructions is not, in my opinion, "attitude", and, given the amount of time I put into an analysis of your problem, I am slightly disappointed that you would feel that way.

Regards

You are welcome!

May you pls mark your thread as SOLVED as per forum rules?

Perhaps you mis-interpreted XOR's reason for asking the question he did. I have seen many instances where a member (new or otherwise) has just entered an array formula by just using ENTER, instead of CSE - it looked to me like he was confirming that you knew how to enter an array - many members dont know how.

1 if the biggest draw-backs of a written communication medium like these forums, is that you cannt see/hear how the question/statement was phrased, so it leaves a lot to interpretation