Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to calculate an average of a column of numbers using 1/2 the value for cells that are flagged by an asterisk in an adjoining column without having to insert a separate column?

share|improve this question
could you give a data example, so your source and your goal is more clear? – Jook Sep 24 '12 at 15:38

If your numbers are in ColumnA (say rows 1:10 and the asterisks are in ColumnB, use =(SUM(A1:A10)-SUMIF(B1:B10,"*",A1:A10)/2)/COUNT(A1:A10)

share|improve this answer
+1 This works fine, I think, as long as B1:B10 has either blanks or asterisk - if there is other text in that range then "*" criterion will also count those, as that works as a wildcard here – barry houdini Sep 24 '12 at 17:55
@barry houdini Asterisk * wildcard effect can be overridden by preceding it with tilde ~. – pnuts Sep 24 '12 at 18:14
Ah, yes, you are correct pnuts, I didn't think of that – barry houdini Sep 24 '12 at 18:47
@barryhoudini - Neither had I until you reminded me! – pnuts Sep 24 '12 at 19:15

This "array formula" should do what you want


confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

share|improve this answer
I prefer your solution - but personally hesitate to recommend array formulae where there are 'viable' alternatives. – pnuts Sep 24 '12 at 18:17
I know what you mean, I was just thinking about the wildcard issue but you answered that one....... – barry houdini Sep 24 '12 at 18:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.