SUM function question

Hi All, I'm currently working my way through a Wiley Finance book on Credit Risk Modeling with Excel. In the appendix of the book which contains a VBA primer the author uses a technique with the SUM function that i've never seen and when i attempt to enter the same code into my sheet i get the dreaded #value error message. Basically the author is taking the summation of the observations (X) minus the mean M (Xi - M). The values of X are contained in the cells B1:B3 and the mean is in the cell B8. the formula used is SUM($B$1:$B$3 - $B$8). Does this appear to be an error? I don't see any errata for the book but this technique is used several times throughout. I'm using Excel 2010 and was hoping that someone could explain this to me. Do i need to have some sort of option turned on to use this technique? Thanks in advance for any help.

It looks wrong to me.

Try one of

=SUM($B$1:$B$3,-$B$8)

or

=SUM($B$1:$B$3)-$B$8

BTW, I don't get #VALUE, I get a result that is essentially B1-B8. A further thought is that it is meant to be a formula of the type

SUM(B1-B8,B2-B8,...)

in which case it should be array entered (commit with Ctrl-Shift-Enter, not just Enter). Was this mentioned anywhere?

Thanks for the reply Bob! No there is no mention of an array being entered but the solution you mentioned appears to work and provide the answer that I need. Also when I use the Ctrl-Shift-Enter solution it transforms the text in the formula bar from =SUM($B$1:$B$3 - $B$8) to {=SUM($B$1:$B$3 - $B$8)} the brackets weren't included in the text. I'm not sure if the author does mention the Ctrl-Shift-Enter method in the book or if he assumes it was common knowledge. Thanks a ton though, i can know proceed through the examples!

I have one other question. How can i then square the result. The SUM problem that i was having is part of a larger equation and I had isolated that part of the formula for simplicity's sake. Say I wanted to have =SUM($B$1:$B$3 - $B$8)^2, how would that be possible? The Ctrl-Shift-Enter solution doesn't work with the power. Thanks again.

I assume you want to square each intermediate result before summing, so use

=SUM(($B$1:$B$3 - $B$8)^2)

again array-entered.

I am surprised the author didn't mention array-entering, it is not common knowledge, in fact it is one of the most frequent error for Excellers.

Bob you are a life saver!! Thanks that worked perfectly. I don't want to say that the author didn't mention it, i've only just begun reading this book,so it's entirely possible that i missed it. But thanks to you, I can now continue.