[Solved] Help with formula in spreadsheet

Discuss the spreadsheet application

[Solved] Help with formula in spreadsheet

Postby krabban » Fri Mar 22, 2019 4:08 pm

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?
Attachments
forum redigering.ods
(25.42 KiB) Downloaded 8 times
openoffice.png
Last edited by Hagar Delest on Mon Mar 25, 2019 10:09 pm, edited 2 times in total.
Reason: tagged solved
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm

Re: Help with formula in spreadsheet

Postby Lupp » Fri Mar 22, 2019 5:02 pm

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.
Attachments
aoo97462splitBySign_1.ods
(13.7 KiB) Downloaded 7 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Help with formula in spreadsheet

Postby krabban » Fri Mar 22, 2019 5:15 pm

Lupp wrote: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.
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm

Re: Help with formula in spreadsheet

Postby RusselB » Fri Mar 22, 2019 7:44 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5502
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Help with formula in spreadsheet

Postby krabban » Fri Mar 22, 2019 7:55 pm

RusselB wrote: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
Attachments
forum redigering.ods
(25.42 KiB) Downloaded 16 times
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm

Re: Help with formula in spreadsheet

Postby krabban » Fri Mar 22, 2019 8:43 pm

Lupp wrote: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"
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm

Re: Help with formula in spreadsheet

Postby John_Ha » Fri Mar 22, 2019 9:09 pm

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.
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6815
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help with formula in spreadsheet

Postby Lupp » Sat Mar 23, 2019 12:18 am

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.
Attachments
aoo97462specialBySign_1.ods
(31.42 KiB) Downloaded 14 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Help with formula in spreadsheet

Postby krabban » Sun Mar 24, 2019 5:02 pm

Lupp wrote: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.
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm

Re: Help with formula in spreadsheet

Postby thinman3 » Sun Mar 24, 2019 6:25 pm

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 ??
3.4.1 & 4.1.5 on MS Windows 7 Pro x64
thinman3
Volunteer
 
Posts: 381
Joined: Sat Jul 11, 2009 8:53 pm

Re: Help with formula in spreadsheet

Postby krabban » Mon Mar 25, 2019 2:31 pm

thinman3 wrote: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.
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm

Re: Help with formula in spreadsheet

Postby RoryOF » Mon Mar 25, 2019 2:43 pm

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.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29577
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Help with formula in spreadsheet

Postby krabban » Mon Mar 25, 2019 3:03 pm

RoryOF wrote: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.
Openoffice 4.1.6 windows 7
krabban
 
Posts: 9
Joined: Fri Mar 22, 2019 3:48 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests