Excel - determine average using 1/2 value for flagged cells

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?

-
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)`

-
+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

`=AVERAGE(A1:A10/IF(B1:B10="*",2,1))`

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

-
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