Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
3  
could you give a data example, so your source and your goal is more clear? –  Jook Sep 24 '12 at 15:38
add comment

2 Answers

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
2  
+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
2  
@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
add comment

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

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
add comment

Your Answer

 
discard

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.