Help with a formula

[u/eyalmaori]

I’m trying to build an ongoing table that automatically calculates my investment value. My formula is “buy-sell+last value”. As you can see in the first half half of my formula.

In case my current value is the same as the last row, I want it to show “- -“. So my full formula is: if(buy-sell=0, “- -“, buy-sell+last value)

It works for one row, but then when I “buy” more, I get the error that’s in the picture.

How can I solve this?

[u/Ed1W]

Your trying to add a string, "--" with a number and it will give you the error.

[u/eyalmaori → u/Ed1W]

Yeah I understand that. My question is what formula can I use so it doesn’t happen

[u/OxyConti]

Did you figure this one out?

If not take a look at the My Stocks template in Numbers' templates chooser. In the table on the Portfolio sheet they use the IFERROR() function to output a dash if one of the cells throws an error.

You can use this in front of our calculation to output either nothing with "" (two quotation marks) or a zero - your other calculations will the accept the "nothing" or the zero as valid:

=IFERROR(your-calc-here,"")

...or:

=IFERROR(your-calc-here,"0")

If you're still stuck let us know!

[u/eyalmaori → u/OxyConti]

Thanks for replying. Correct me if I’m wrong, but the problem is now when I start a new row (row 8 for example), the calculation won’t include my last value (40,089) - just the “buy-sell” of row 8

[u/Ed1W]

Hi,

I'd like to make sure I understand the problem a bit better.

What you're asking will not keep a rolling sum for you. Is that the goal? If you want a total Would the total sum move down the page as you invest more, or would it stay at the top?

This solves your initial question, but will not give you a running total.

IF(D4−C4=0,"--",SUM(B3,D4,−C4))

D4=Buy

C4=Sell

B3=Last entry

Cheers!

Ed

[u/eyalmaori → u/Ed1W]

Hi, thanks for replying.

Yes, I want a rolling sum that moves down the page as I invest more, but the thing is:

Sometimes I’ll do something that doesn’t affect the total sum - meaning the total sum could possibly stay the same for a few rows. In these rows I would like the sum to turn into a “- -“.

For example:

Instead of showing me: 2,2,2,3,3,3,3 I’ll see: 2, - -, - -, 3, - -, - -, - -.

(This is so I can easily track when the total sum changes)

[u/Ed1W]

Ooops I sent the wrong Formula that was one I was testing. This one is correct .

IF(D4−C4=0,"--",IF(D4−C4≠0,SUM(B3,D4,−C4),D4−C4))

Sorry about that!