Libreoffice Calc increment a cell value with respect to a comparison between 2 other cells

[showtown]

Hello, I’m a real novice & could do with some help.

The value of Fridays data is compared with Thursday - font & background will change colour depending on whether Fridays value is higher or lower. (conditional formatting working fine)

What I would like is for the related up or down data to increment +1 depending on whether Friday is higher or lower compared to Thurs. Each day I add a new Day column which I paste data into, each new day will be compared with the previous.

I have some small coding experience from way back, I was expecting to be able to put a formula into the up & down cells, something like this =IF(B2>=A2,D2=D2+1) Not so simple though. Any help or pointing in the right direction appreciated

Davelibre office spreadsheet.ods

[Zizi64]

Please upload a real ODF type sample file here.

You can not modify an another cell (directly) by a normal formula, but you can modify the same cell only (where the formula was called from).

[showtown]

Thanks for getting back to me please find posted ODS file. I think I need to use a variable, but have no idea how to assign a value to it. Libreoffice help does show how to name variables but I cannot find an example of how to use them or increment them. If you are able to suggest a way of doing what I need, that would be great. Thanks

[showtown]

I managed to get close to what I wanted wasn’t easy for a novice but in the end I used “macros” to create a small “variable function” See my example below “A” and “B” columns are compared, conditional formatting is used to change the cell and font colours depending whether “B” is greater than or equal to “A”. columns “F” and “G” do similar but call a function which increments a variable. Depending on the result, columns D and E add the historical data and incremented variable.

input higher and lower values into column “B” to see it in action

I’m still struggling to get it to work when I insert a column aft “B” though. anyone with any suggestions are welcome.

Cheers

project2.ods

[anon73440385]

Hello,

may be the following is what you require:

In F2: =IF(OFFSET(F2;0;-4)>=OFFSET(F2;0;-5);"CALL UPTLW")
In G2: =IF(OFFSET(F2;0;-4)<OFFSET(FF2;0;-5);"Call DOWNTLW")

See the following modified version of your sample file:

Q295798-Project2.ods

Note(s):

• The OFFSET() based solution only works, if the shift to values being compared in IF never changes to any other values (which is now -4 and -5). In other word the solution compares values 4 and 5 cells left to the first cell containing the IF (i.e. F2) (you could also use in cell G2: =IF(OFFSET(G2;0;-5)<OFFSET(G2;0;-6);"Call DOWNTLW")). If you insert a column after B cells shift to F2 and H2 and formulas get adapted to =IF(OFFSET(G2;0;-4)>=OFFSET(G2;0;-5);"CALL UPTLW") and =IF(OFFSET(F2;0;-4)<OFFSET(FF2;0;-5);"Call DOWNTLW") but still compare cells 4 and 5 cells left to the formula…

• Replaced uptlw() and downtlw() by "CALL UPTLW" and "Call DOWNTLW", since you did not add the function to the sample file and hence otherwise wasn’t able to test the solution.

Hope that helps.

[showtown]

Thank you, I did not know about the OFFSET function, but it sounds obvious now.
I am out at the moment but look forward to looking at your solution when I return.

Thanks

[showtown]

Thank you for your efforts, this is exactly what I need. I have now added “Offset” to my knowledge base. I have a lack of syntax and am just getting to grips with Macro & Variable use, which I am studying at the moment, the forum is a great asset…

Regards Dave