average with exception of some cells?

  • Thread starter Thread starter cpliu
  • Start date Start date
C

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,
 
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
 
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")
Click to expand...

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

???

Thanks!
 
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
 
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
Click to expand...
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,
 
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
Click to expand...
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,
 
Back
Top