{=SUM(ABS($Q$5:$Q$651))}

[rockhammer]

I am looking at spreadsheets someone else built and seeing cells with
formulae like what's shown in subject.

I can see what it is supposed to do which is to sum the absolute value of
each cell referenced, which is something I want to duplicate elsewhere.

However, when I try to put that formula in the cell, it gives me #value! So
I'm just wondering how exactly was this done. I suppose the curly brackets
mean something but I don't know what.

Thanks.

 

[PCLIVE]

It's an array formula. You don't type in the brackets. When your cursor is
in the formula bar, press Ctrl+Shift+Enter.

Regards,
Paul

 

[T. Valko]

Try it like this and then you don't have to worry about the brackets:

=SUMPRODUCT(ABS($Q$5:$Q$651))

About the brackets....

The brackets mean this is an array formula. Array formulas are entered
differently than a regular formula. After you type in a regular formula you
hit the ENTER key. With an array formula you *must* use a combination of
keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is,
hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formulas in the brackets. You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.

 

[rockhammer]

Thanks, Paul. It works now.

It's an array formula. You don't type in the brackets. When your cursor is
in the formula bar, press Ctrl+Shift+Enter.

Regards,
Paul