[Solved] Re-calculating rows but moving formula...

Discuss the spreadsheet application

[Solved] Re-calculating rows but moving formula...

Postby withnail » Sat Apr 04, 2020 12:51 pm

Hi all,
See attached, a picture says a thousand words, or asks 1000 questions ;-)
I know how to do basic formula's to do calculations, but in my particular problem I don't know how to structure it all...
Thanks!
Attachments
Webp.net-resizeimage.jpg
Last edited by Hagar Delest on Mon Apr 06, 2020 7:28 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby Lupp » Sat Apr 04, 2020 1:21 pm

withnail wrote:...a picture says a thousand words...

Probably nearer to reality:
A picture lies a thousand words.
A picture misleads a thousand words.

Please attach a real example (.ods file) containing your formulas.
Firts of all: Describe clearly what you eventually try to achieve.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-calculating rows but moving formula...

Postby withnail » Sat Apr 04, 2020 2:18 pm

Ok,see attached sheet.
I have this basic formula =IF(B4=0;(3*(C3+10));0)

I have 10 columns (C-L) numbered 1-10.
Column A reflects these column numbers, column B is the IF part of the formula.
I want column 1 to use this formula when 1 appears in column A.
And column 2 to use this formula when 2 appears in column A.

Also the result of the first formula in C3 is 30, but I don't want it to show 30 here.
I want to divide 30 by 10 and insert the result (3) into all cells in the row.
Same again in the next row but then the result needs to also add the previous result from previous cell.

In the example, the upper table (with formula's) is what I have, the lower table (without formula's) is where I want to get to....
Attachments
oo.ods
(10.01 KiB) Downloaded 12 times
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby FJCC » Sat Apr 04, 2020 4:36 pm

It is not clear to me what you want to happen when the cell in column B does not equal zero. Are the results in C3:L12 what you are looking for?
Attachments
oo_fjcc.ods
(11.77 KiB) Downloaded 12 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Re-calculating rows but moving formula...

Postby withnail » Sat Apr 04, 2020 5:02 pm

Yes, that's great, many thanks! That's a much better way than the direction I was going...

It's only of importance if there is a zero in column B, if it's anything else then the result is also just zero.
The only thing I could do with now is to round off the results to 2 decimal points as they are monetary values.
Not sure where exactly to enter the ROUND function to get the cleanest formula...
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby withnail » Sat Apr 04, 2020 5:50 pm

FJCC, I've just realized there's 1 small problem in your sheet.
You see in D4 it divides and adds to all the other cells, but not to the cell where the formula is being calculated.
All the other cells are 6,90 but D4 only goes up to 3,90. D4 should also be 6,90.
I can't work out why it does that.
H8 is the same, it adds 5,07 to the rest of the row, but not ti itself.
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby FJCC » Sat Apr 04, 2020 9:43 pm

I am confused about just what you want to add to what. Could you fully fill out a few rows of the table with values and post it here? Then I can try to match that.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Re-calculating rows but moving formula...

Postby withnail » Sat Apr 04, 2020 9:53 pm

If you look at H8, the sum of the formula is 5,07 which is correct.
This 5,07 then gets added on to the other cells in the row 8 including their previous value in above cells, this is also correct.
But cell H8 becomes just 5,07, it should become 5,07 + 6,90 (the value of H7) but for some reason it''s not adding up.
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby FJCC » Sun Apr 05, 2020 1:46 am

H8 cannot contain both 5.07 and 6.90 so the IF() calculation cannot be stored there as it currently is. I tried another solution and got all tangled up. I will come back to this later.
It would still really help if you uploaded a completed table. I do not understand the purpose of your calculation and might easily misunderstand what you want.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Re-calculating rows but moving formula...

Postby withnail » Sun Apr 05, 2020 11:13 am

The sheet is for tracking and calculating the results of a Forex trading strategy.
I keep entering data into column B which will always be a long list of 1's and 0's.
I have 10 financial instrument selections (columns C-L) where trades occur in chronological order (1-10 repeated in column A).
The sheet is doing exactly what I want it to do except for the cells I've marked in bold red.
Like you say, the cells cannot contain both values. This is the only problem I can't get around.
Eventually there will be some more counting and summing formula's added but I can do those myself.
Attachments
oo_fjcc (1).ods
(16.02 KiB) Downloaded 9 times
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby FJCC » Sun Apr 05, 2020 8:31 pm

I made a new table starting in row 25 and using a helper columns
Attachments
oo_fjcc_v2.ods
(15.09 KiB) Downloaded 13 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Re-calculating rows but moving formula...

Postby withnail » Sun Apr 05, 2020 8:41 pm

The Holy Grail :-) Thanks!

I was thinking about a helper column too and I've been trying all day to figure out how the offset function was working in your previous sheet, but I couldn't for the life of me work it out...
I only have one more question, where can I best place the ROUND function to round the values off at 2 decimal points?
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby FJCC » Sun Apr 05, 2020 9:19 pm

I would wrap the whole function in ROUND
Code: Select all   Expand viewCollapse view
ROUND(IF(AND($A27 = COLUMN() -2;$M27 = 0);0;C26 + $M27);2)

Beware of rounding error. You can set the cell format to have two decimal places and not lose precision, though maybe that is not a concern for you.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Re-calculating rows but moving formula...

Postby withnail » Sun Apr 05, 2020 9:26 pm

I just found a round to 2 decimal places function in the Options menu and seems to work fine, so I'll leave it like that.
Thanks again, really helped me out that :-)
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby Lupp » Sun Apr 05, 2020 10:01 pm

NEVER use Option settings which may influence numeric calculations.

If you need some rounding, use the ROUND() function or one of the related additional rounding functions.
If you want a specific format concerning the number of decimals, use formatting.
Don't mix these two very different aspects.

The option you are supposed to mean is under >Tools>Options>OpenOffice Calc>Calculate>> (enable 'Limit decimals for general number format' - and then a spin-button field).
This option is only relevant for the formatting and will not impose any rounding.

NEVER EVER enable Precision as shown!! It messes up everything.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-calculating rows but moving formula...

Postby withnail » Sun Apr 05, 2020 10:08 pm

I'll check and try the ROUND function, thanks!
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby withnail » Sun Apr 05, 2020 10:17 pm

When I input ROUND function like in FJCC's example I indeed get an error.
I've formatted the cells to 2 decimal places instead.
The slight loss of precision isn't a problem for me.
Thanks for the tip.
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby Lupp » Sun Apr 05, 2020 10:52 pm

If you have a cell containing '=MyFormula' and you get returned a NUMBER there, and no error, how can then the same cell changed to the formula =ROUND(MyFormula;2) return an error? What error message do you get?
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-calculating rows but moving formula...

Postby withnail » Sun Apr 05, 2020 11:05 pm

Strange, I just tried it again and no error!
The first time I was getting error 522 I think.
This is my formula now - =ROUND(IF(AND($A3 = COLUMN() -2;$BA3 = 0);0;AZ2 + $BA3);2)
Anyway, sorted now, thanks.
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Re-calculating rows but moving formula...

Postby keme » Mon Apr 06, 2020 1:04 am

Beware of rounding on forex trades!

Transactions may be to the currency (unit) or to the penny (two decimals) but exchange rates have 4 (sometimes 5) decimals to the currency unit. When you round a calculation, further calculations based on that rounding may have lost precision. If you do large volume trading, you want your shadow accounts to reflect actual state. In most cases, you don't want to round intermediate calculations to two decimals.
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Re-calculating rows but moving formula...

Postby withnail » Mon Apr 06, 2020 8:38 am

Thanks Keme, I'll keep that in mind :-)
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: [Solved] Re-calculating rows but moving formula...

Postby keme » Mon Apr 06, 2020 8:59 am

withnail wrote:Thanks Keme, I'll keep that in mind :-)

I'm sure that you will. Didn't mean to come out as condescending here. You undoubtedly know more about the trade than I do.

My comment was just a "reflex posting", intended to warn about potential accidents. It is all too easy to "slip", and ruin a calculation because you round in the wrong place. (This I know from personal experience). Absolutely no offense intended.
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Re-calculating rows but moving formula...

Postby withnail » Mon Apr 06, 2020 10:16 am

keme wrote:
withnail wrote:Thanks Keme, I'll keep that in mind :-)

I'm sure that you will. Didn't mean to come out as condescending here. You undoubtedly know more about the trade than I do.

My comment was just a "reflex posting", intended to warn about potential accidents. It is all too easy to "slip", and ruin a calculation because you round in the wrong place. (This I know from personal experience). Absolutely no offense intended.



Not at all Keme, I didn't read it as condescending at all :-)
You're simply correct. The details I'll sort out later, I was just so concentrated on the formula structure, it was driving me mad.
Thanks!
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests