Sigma formula function in Numbers

Question

yceh Author

Level 1

8 points

Sigma formula function in Numbers

Hi,

I am looking for a function for sigma formula working in Numbers. ex, "Sigma function" 2+5*(X-3), X=1,2,3,....n.

After searching, I found a combination of formula for Excel works:

=SUMPRODUCT(2+5*(ROW(INDIRECT("1:"&$A$1))-2))

But this formula doesn't work in Numbers. The reason seems the Numbers treat ROW function different to Excel, that is, if you insert ROW(INDIRECT("1:"&A1), Numbers returns only 1 but Excel returns a column of rows 1,2,3,....

How can I create a sigma formula working in Numbers? Thank you.

Posted on Aug 4, 2020 7:02 PM

Reply

Answer 1

yceh Author

Level 1

8 points

Aug 7, 2020 5:43 PM in response to Badunit

Thank you for the reply.

Till now, the only way I found is creating another table that listing all the n (n=1,2,3,...ect) and its results. and use function SUMIF(A1,">"&n, n results column) to the sigma equation. But it is kind of verbose in anyway.

Reply

Answer 2

Badunit

Level 7

30,288 points

Aug 7, 2020 5:55 PM in response to yceh

That was what I was thinking of.

The Excel formula is an "array" formula, which is not something available in Numbers. In Excel it is entered a special way to make it an array formula vs a regular formula. It is calculating the ROW() function for ALL the rows in the formula and making an array of the results, which it then uses in the rest of the formula to do all the calculations for all the rows. If entered as a regular formula, it would calculate only the first ROW() and do one calculation, which is what Numbers is doing. The other thing the formula relies on is Excel's monolithic worksheets. By default they have many many rows and columns. The worksheet (table) has to have at least n rows for the formula to not throw an error and Excel has that by default. In Numbers, the default table is only 22 (or 10) rows. If you need n=900,000, for example, it would require you manually create a table with 900,000 rows.

Reply

Answer 3

yceh Author

Level 1

8 points

Aug 7, 2020 6:07 PM in response to Badunit

You are absolutely right.

It seems no function for Numbers to return an array as result (or I don't know it?). Sigma needs a sequential numbers to have results. And the reason might be like what you mentioned Excel is monolithic worksheets but Numbers is not.

Reply

Answer 4

Badunit

Level 7

30,288 points

Aug 7, 2020 9:02 PM in response to yceh

Is that the actual equation? It can be broken down into a formula also

where m= -0.3

The sum = (1 + (1 - e^((n-1)*m))/(e^-m - 1) - ne^nm)/(e^-m - 1) -5*(1-e^nm)/(e^-m - 1)

Or, in Numbers format where n is in cell D1,

=(1+(1−EXP(−0.3×(D$1−1)))÷(EXP(0.3)−1)−D$1×EXP(−0.3×D$1))÷(EXP(0.3)−1)−5×(1−EXP(−0.3×D$1))÷(EXP(0.3)−1)

I'd show the solution but it is so hard to read (and write) this stuff as single line text vs true equation format.

Reply

Answer 5

Joseph_S.

Community Specialist

Aug 7, 2020 12:26 PM in response to yceh

Hello yceh,

We understand that you're having issues using the Sigma function on Numbers, and we'd like to help.

We've located some links that may help you with this issue. Please check these out:

Calculate values in Numbers on Mac

Formulas and Functions Help

Functions Browser

Best regards.

Reply

Answer 6

yceh Author

Level 1

8 points

Aug 7, 2020 6:28 PM in response to Badunit

WOW!! That is awesome, really. Thank you for the equation simplify ;)

No. The equation is just for demonstration. The equation I need is involving the (natural) exponential function like ∑e^-0.3*(n-5), n=1,2,3..... which I really wish I can break it down.

But I think simplifying ∑x for x=1...n to n(n+1)/2 is such a brilliant way. Good job!

Reply

Answer 7

Badunit

Level 7

30,288 points

Aug 7, 2020 9:12 PM in response to yceh

It was a fun challenge but now I am second guessing what the equation was.

I did (e^-0.3x)*(x-5) with x=1..n but that isn't what you wrote. Was it e^(-0.3*(x-5)) or (e^-0.3)(x-5) or did I stumble into the right one the first time? Either of those should be solvable, too.

Reply

Answer 8

yceh Author

Level 1

8 points

Aug 7, 2020 9:27 PM in response to Badunit

;)

I scratched days for presenting this equation in Numbers. You just approached with a total different angle with an elegant solution, really.

It is such a beautiful way like you solve a difficult function question with a simple geometry solution.

Reply

Answer 9

Badunit

Level 7

30,288 points

Aug 7, 2020 5:31 PM in response to yceh

I am thinking that in Numbers you will have to actually have n equations that you then SUM. But I'll keep thinking.

Reply

Answer 10

yceh Author

Level 1

8 points

Aug 7, 2020 9:03 PM in response to Badunit

Man you are GENIUS!!

You just solve a Numbers function problem with mathematic solution in such an elegant way!

BIG BIG Thank you!! :)

Reply

Answer 11

Badunit

Level 7

30,288 points

Aug 7, 2020 10:32 PM in response to yceh

Thanks. I enjoy these problems. Let me know if I solved the wrong equation, though.

Reply

Answer 12

Badunit

Level 7

30,288 points

Aug 7, 2020 6:24 PM in response to yceh

Is the equation you provided the sum of (2+5*(x-3)) from x=1 to n the actual equation you are wanting an answer to? It can be simplified to not require calculating each part and adding them up.

∑(2 + 5(x-3)) for x=1...n

=∑(2+ 5x - 15) for x=1...n

=∑(-13 + 5x) for x=1...n

=-13n + 5∑x for x=1...n

=-13n + 5n(n+1)/2

I was playing around in Excel and you are correct; ROW works differently in Excel. ROW(INDIRECT("1:"&A1)) will create an array of results in Excel even when not entered as part of an array formula. In Numbers it gives the first value only.

Reply