average with exception of some cells?

[cpliu]

How can I exclude a few cells in calculating the average, maybe if it
has color or whatever the best way to distinguish it from the rest?

I can probably manually delete them first, write down the average,
revert to previous, but it is not automatic.

Thanks,

 

[Per Jessen]

Hi

Values in A1:A10, in column B you can mark values to exclude from the
calculation with an 'x', and use this formula to calculate the average:

=SUMPRODUCT(A1:A10,--(B1:B10<>"x"))/COUNTIF(B1:B10,"<>x")

Regards,
Per

 

[cpliu]

Values in A1:A10, in column B you can mark values to exclude from the
calculation with an 'x', and use this formula to calculate the average:

=SUMPRODUCT(A1:A10,--(B1:B10<>"x"))/COUNTIF(B1:B10,"<>x")

Thank you for your help. I got an error with A10 highlight after copy-
and-pasting your code.

???

Thanks!

 

[Per Jessen]

Did you pasted my formula in A10, because then you will get a circular
reference error.

Insert the formula in A11 and see what happens.

Regards,
Per

 

[cpliu]

Did you pasted my formula in A10, because then you will get a circular
reference error.

Insert the formula in A11 and see what happens.

Regards,
Per

Thank you, Per. I did enter in A11. It gives "The formula you typed
contains an error." message. Click "OK" it highlights "A10,". If I
take "," out, it pops up the error messaga again, and highlights
"B10,"".

The formula looks right when searching around the internet. I can't
even get COUNTIF(B1:B10,"<0") for tally the total of negative numbers
to work.
I tried both Office 2003 and 2007 with the same result.

Thanks for the help,

 

[Per Jessen]

I think there is something wrong in your sheet, have you tried to copy your
data to an new workbook and see if the error persists?

If you want you can send me a copy of your workbook, and I will see if can
find the error.

The formulas should work.

Regards,
Per


"cpliu" <[email protected]> skrev i meddelelsen

Did you pasted my formula in A10, because then you will get a circular
reference error.

Insert the formula in A11 and see what happens.

Regards,
Per

Thank you, Per. I did enter in A11. It gives "The formula you typed
contains an error." message. Click "OK" it highlights "A10,". If I
take "," out, it pops up the error messaga again, and highlights
"B10,"".

The formula looks right when searching around the internet. I can't
even get COUNTIF(B1:B10,"<0") for tally the total of negative numbers
to work.
I tried both Office 2003 and 2007 with the same result.

Thanks for the help,