45

What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table:

      A    B    C
1    1.5   0    0
2   -0.5   0    0
3    4     0    0
4    5     0    0

Then, what is the following formula doing?

= A1:A4^{1,2,3}
4
  • -1 You should have tried the formula before writing it. And as an answer pointed out, your formula gives an error. Even the first part of it =A1:A4 gives an error. By the way I notice that 2^{3,4,5,6,7} seems to do the same as 2^{3} so the numbers listed after the first number in the curly brackets don't seem to have any affect there – barlop Mar 4 '15 at 19:36
  • caret is for "to the power of" a.k.a exponent / index / order. – barlop Mar 4 '15 at 19:38
  • 22
    @barlop You might want to reconsider your downvote. There is a sensible way to use this formula that is completely opaque to a non-experienced Excel user -- see my answer. – Excellll Mar 4 '15 at 19:51
  • 3
    @Excellll ok i'll check it out, interested to try your ans. i've removed my downvote – barlop Mar 4 '15 at 19:57
70

The caret signifies a power operation. The curly bracketed expression, {1,2,3}, is an array containing the values 1, 2, and 3.

As noted by @JasonClement, this formula only returns a value when entered as an array formula with Ctrl+Shift+Enter. As an array formula, the result is a 4-by-3 matrix containing the values below.

A1^1  A1^2  A1^3
A2^1  A2^2  A2^3
A3^1  A3^2  A3^3
A4^1  A4^2  A4^3

When entered in a single cell, however, only the top-left value of the matrix is returned.

The secret to this formula is that it must be entered as a single formula into a range the same size as the matrix. To do this, select an empty 4-by-3 range, e.g. D1:F4. Without changing the selection, click in the formula bar and type the formula. Then press Ctrl+Shift+Enter. You'll see the full matrix of results printed in the range you selected.

enter image description here

2
  • Thank you very much for your complete response. Actually this formula was part of another formula which was supposed to calculate the coefficients of a polynomial curve fitting. – Kevin Bell Mar 5 '15 at 0:01
  • 5
    The first part of this question that I read was its title (duh), which says “What does “A1:A4^{1,2,3}” mean in an Excel formula?” It can be used as a parameter to a worksheet function (in a single cell). For example, if A1 contains 100 and A2 contains 2, then =SUM(A1:A2^{1,2,3}) (as an array formula) evaluates to 1010114, which is 100+100²+100³+2+2²+2³ (1010100+14). – G-Man Says 'Reinstate Monica' Mar 5 '15 at 2:09
9

The caret operator is for specifying an exponent. For instance, 2^4 will return 2 to the 4th power (2*2*2*2), which is 16.

Curly brackets like this are a way of defining an array, which could be used wherever a range could also be specified. For instance, using the INDEX formula with an array like so: =INDEX({2,5,7,9}, 4) will return the 4th item in the array: 9.

I have no idea what this formula is attempting to do. As it is, it results in an error. You can enter it as an array formula by pressing CTRL and SHIFT while pressing ENTER, but that will only return the value of the first cell (A1) raised to the first value in the given array (1), which is the same as 1.5 to the 1st power, which is 1.5.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.