Formula in Calc

[Huskey]

Hi there. This (small) problem drives me crazy. Can somebody please help me.

This is my goal:

But problem is (see below): At the end of each month I manually insert the Account Total for the relevant month. In row 5 is the formula: current Account Total - previous month’s Account Total with the exception of January where it says =SUM(B6-N3). As you can see in the example below, the output in N5 (=SUM(B5:M5) is wrong because D5 has been automatically added.

How can I avoid this? Is there maybe a command such as: only fill D5 if there is a value in D6? That’s just a thought of mine. I would really appreciate if someone could help me. Many thanks.

[joshua4]

Attaching docs is a lot more helpful than images. N5 is #Ref! because B5 is #Ref!. That Ref error is caused by

@Huskey

=SUM(B6-N3)

I think you just want =B6-N3

General advice might be:

1. Attach an example ods file
2. Avoid tucking in aggregate data–like here you have totals under month headers. Keep your data in pure table form, then create a report sheet separately, or study up on Pivot Tables, which might do almost everything your want automatically.

[Huskey]

@joshua4
Thanks for reply. Attached is doc.
LO Forum.ods (22.8 KB)

[erAck]

In C5 enter =IF(ISNUMBER(C6);C6-B6;0)
Then copy cell C5 to clipboard, select D5:L5 and paste. Note that you’ll have a gap in December because the way you setup the calculation using the same formula in M5 doesn’t make sense with M6 being =SUM(N3+N5) and N5 being =SUM(B5:M5) because that would

1. yield the ISNUMBER(M6) part FALSE (because M6 is a formula with an error result not a number) so the result would always be 0
2. lead to a circular reference (M6 → M5 → N5 → M6), you’d get Err:522 in N5 and M6 if you did.

Design a better spreadsheet… where a month’s total isn’t abused as a year’s total.

And as joshua4 already mentioned you can omit all SUM() calls for single scalar results like B6-N3 or N3+N5, it’s superfluous.

[joshua4]

Here’s an example of erAck’s “Design a better spreadsheet.” I cheated and converted my ISBLANK logic to his better ISNUMBER logic before uploading it.
LO Forum.ods (27.1 KB)

[Huskey]

@joshua4

Firstly, many thanks for your help. Unfortunately, I was unsuccessful to duplicate your example (Err:501 in C5). Could you do me a huge favour and insert the formulas in the C column. Many thanks in advance.
LO Forum 2.ods (12.2 KB)

[joshua4]

Feel free to just whole-sale copy the demo sheet, or move that sheet into your workbook. I think your issue with the 501 error is just that AND wraps the two ISNUMBERS. You can just go to C5, press F2 then Ctrl+A then Ctrl+V after marking and copying this:

=IF(AND(ISNUMBER(B4),ISNUMBER(B5)),B5-B4,"")

Also, if you are interested in more Excel/LO, definitely check out the following channel. Look for her videos on basic topics first…you’ll start to pick up on what level a video is at pretty quickly. Don’t worry about the latest-and-greatest stuff (which is mostly Office 360, anyway).

https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA

The basics are the same in Calc or Excel.

[Huskey]

@erAck: Thank you for your reply.

@joshua4: Thank you for your help. All good now. And yes, I’ve subscribed to the YouTube channel you’ve recommend.