[Solved] Help with formula in spreadsheet

[krabban]

Can anyone help me with the formula, how I summarize plus and negative numbers in different columns, negative numbers to column marked with red. see attached file.
I would also remove the characters that In market with a blue box in the attached file, if this is possible without changing the mathematical formula in the 50 lines?

[Lupp]

Wellcome to the forum!

Alas!
How shall I tell you anything about formulas contained in your sheet if I only have this image? Please attach the real thing (spreadsheet document). If there is confidential content, please remove or anonymize it in advance.

The editor here offers the tool for "Upload attachment" below the frame for editing.

What I could do for you without knowing the details you find in the attached example.

[krabban]

Wellcome to the forum!

Alas!
How shall I tell you anything about formulas contained in your sheet if I only have this image? Please attach the real thing (spreadsheet document). If there is confidential content, please remove or anonymize it in advance.

The editor here offers the tool for "Upload attachment" below the frame for editing.

All right, now I have uploaded the entire spreadsheet.

[RusselB]

With regrets, I must inform you that the spreadsheet did not upload, as there is no link in your post to the uploaded file.
The forum has a 128k file size limit. If it is bigger than that, you'll have to upload it to a file sharing service, like MediaFire or Dropbox, then post a link here to the address where the file is contained.

[krabban]

With regrets, I must inform you that the spreadsheet did not upload, as there is no link in your post to the uploaded file.
The forum has a 128k file size limit. If it is bigger than that, you'll have to upload it to a file sharing service, like MediaFire or Dropbox, then post a link here to the address where the file is contained.

What I can see is the file there and is downloaded 3 times.
Tests upload again

[krabban]

Wellcome to the forum!

Alas!
How shall I tell you anything about formulas contained in your sheet if I only have this image? Please attach the real thing (spreadsheet document). If there is confidential content, please remove or anonymize it in advance.

The editor here offers the tool for "Upload attachment" below the frame for editing.

What I could do for you without knowing the details you find in the attached example.

I have downloaded your formula but won't it work? There must be something more in the formula when two numbers should subtraction column "G" and "I"

[John_Ha]

Look up the IF function in the manual or in Help.

Then do something like IF(number is negative; copy it to column X; copy it to column Y). All negative values are now copied to column X and all positive values are copied to column Y. Decide how to handle zeros.

You will find much useful information in the User Guides, the Writer, Base and Calc Tutorials and the AOO Frequently Asked Questions. May I suggest you bookmark the pages.

[Lupp]

Basically I don't understand for what reasons all (?) your formulae referenced ranges over48 rows. You did this in a way where the so called intersection takes place: Only the reference to the one cell of any column in the same row as the formula itself is evaluated then. This is, IMO, only (if at all) reasonable if the ranges are named and referenced by their names. Some may also find them cool if absolute row addressing is used. With explicit references based on relative addressing there's no sense in it.

However, your main concern - as I iunsderstood it - was to split some preliminary results in two columns depending on the sign. To avoid highly complicated formulae and inefficient multiple calculations such a split is best done based on a helper column which may get hidden after its contents were created.

An unsolicited but generally useful advice: Don't worry in any way about formats for the view or "prettyprint" or alike as long as not the functionality you want is correctly implemented. The only formatting you may need to consider is that for numbers.

See attachment.

[krabban]

Basically I don't understand for what reasons all (?) your formulae referenced ranges over48 rows. You did this in a way where the so called intersection takes place: Only the reference to the one cell of any column in the same row as the formula itself is evaluated then. This is, IMO, only (if at all) reasonable if the ranges are named and referenced by their names. Some may also find them cool if absolute row addressing is used. With explicit references based on relative addressing there's no sense in it.

However, your main concern - as I iunsderstood it - was to split some preliminary results in two columns depending on the sign. To avoid highly complicated formulae and inefficient multiple calculations such a split is best done based on a helper column which may get hidden after its contents were created.

An unsolicited but generally useful advice: Don't worry in any way about formats for the view or "prettyprint" or alike as long as not the functionality you want is correctly implemented. The only formatting you may need to consider is that for numbers.

See attachment.

Thanks now I got Minus and the plus results to match in different columns.

Another question I have is whether it is possible to do the copy protection worksheet? Was thinking about selling this and didn't want it to be easy to copy.

[thinman3]

Looking at Sheet...Ark 1 (2) , Column I , the formulas increase by 1 from ROW 3 to ROW 6. However, for ROW 7, it increases by 2. Is this what is intended or mistake ??

[krabban]

Looking at Sheet...Ark 1 (2) , Column I , the formulas increase by 1 from ROW 3 to ROW 6. However, for ROW 7, it increases by 2. Is this what is intended or mistake ??

Don't really understand how you mean the error is? I can't find any fault.

[RoryOF]

He means that there is a change in the increase amount from 1 to 2, and asks if this is intended, or should the increment be 1 in all cases.

[krabban]

He means that there is a change in the increase amount from 1 to 2, and asks if this is intended, or should the increment be 1 in all cases.

Okay thanks now I have fixed this.