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

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

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
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...

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

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

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

Lupp
Volunteer

Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

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
OpenOffice 4.1.7
Windows 10
withnail

Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

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

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
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7683
Joined: Sat Nov 08, 2008 8:08 pm

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

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...

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...

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
FJCC
Moderator

Posts: 7683
Joined: Sat Nov 08, 2008 8:08 pm

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

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...

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
FJCC
Moderator

Posts: 7683
Joined: Sat Nov 08, 2008 8:08 pm

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

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
OpenOffice 4.1.7
Windows 10
withnail

Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

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

I made a new table starting in row 25 and using a helper columns
Attachments
oo_fjcc_v2.ods
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7683
Joined: Sat Nov 08, 2008 8:08 pm

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

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...

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
FJCC
Moderator

Posts: 7683
Joined: Sat Nov 08, 2008 8:08 pm

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

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...

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

Lupp
Volunteer

Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

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...

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...

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

Lupp
Volunteer

Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

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...

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.

keme
Volunteer

Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

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...

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.

keme
Volunteer

Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

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