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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rockhammer - 06 Feb 2008 22:18 GMT

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.

Reply to this Message

PCLIVE - 06 Feb 2008 22:25 GMT

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

>I am looking at spreadsheets someone else built and seeing cells with
> formulae like what's shown in subject.
[quoted text clipped - 8 lines]
>
> Thanks.

Reply to this Message

rockhammer - 06 Feb 2008 22:44 GMT

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.
[quoted text clipped - 14 lines]
> >
> > Thanks.

Reply to this Message

T. Valko - 06 Feb 2008 22:39 GMT

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.

Signature

Biff
Microsoft Excel MVP

>I am looking at spreadsheets someone else built and seeing cells with
> formulae like what's shown in subject.
[quoted text clipped - 8 lines]
>
> Thanks.

Reply to this Message