Libreoffice speadsheet help

lindabea

Putting the formula in B3.... IF (B1>B2,B3) works fine as long as whenever I update B1 with a greater value. If I enter a lower value in B1, then B3 gets updated with zero. Logically, it's correct because the condition is false, so B3 is populated with the 'otherwise' value. Looks like I'm going to have to read up about macros

Paul_DNAP

B4 comes into it because you can't do a calculation in B3 and preserve the previous value that is in there, so you need a brand new spare cell to do your calculations and display your result.


But then you want the result of the calculation to be preserved, even after you alter the cells you're doing the calculation, this is even trickier, because of course the B1>B2 will always look at the current value of B1 and not what it used to be previously.


So I can only suggest that the only way to do it would be to manually fill out B3.


Or, you'd need to have some way of preserving the historical data, and then referencing the "else" part of your =IF to the previous entry and not the current entry.

Cornucopia

It would be useful to know whether the requirement is for a single "round" of comparison, or whether the intention is for repeated comparisons, potentially with retention of more than one previous value.

The former case is doable with one additional cell, as suggested. The latter case is a spreadsheet all of its own.

lindabea

Cornucopia wrote: »

It would be useful to know whether the requirement is for a single "round" of comparison, or whether the intention is for repeated comparisons, potentially with retention of more than one previous value.

The former case is doable with one additional cell, as suggested. The latter case is a spreadsheet all of its own.

My intention is for repeated comparisons. So, in B1 I want to enter the latest value which could be higher or lower then the previous value contained in B1. Ideally, the value in B3 should only change when B1 has a larger value then previously entered. So B3 should only ever show the greatest value that B1 ever had.

Paul_DNAP

The only way I can see to do that "greatest value ever had" easily is by retaining the historical data and finding the maximum from the whole set.


That's because, you can't do a logic on the previous value of B1 because the spreadsheet does not know what the previous value was as you've over written it.


You might be able to write a script that puts up a prompt box for the new data which it can store and compare against the old data in the sheet before writing the new entries out, but that's beyond me.

Cornucopia

Have you looked at the MAX function? You can specify a range of cells, and it will return the maximum value across all of them.

https://www.excelfunctions.net/MaxFunction.html

Otherwise, I think you'll need to set out a long list of cells with your values and formulas in them, so as to be able to make multiple comparisons.

WaywardDriver

lindabea wrote: »

Putting the formula in B3.... IF (B1>B2,B3) works fine as long as whenever I update B1 with a greater value. If I enter a lower value in B1, then B3 gets updated with zero. Logically, it's correct because the condition is false, so B3 is populated with the 'otherwise' value. Looks like I'm going to have to read up about macros

That's why the Iterative Calculation option is necessary but appreciate may not be possible in LibreOffice.

DoaM

Some description of the context of what you're trying to achieve might help someone to devise a better scheme. What is this in relation to?

WaywardDriver

WaywardDriver wrote: »

Problem is with iterative calculation - following works in Excel but LibreOffice?
Excel Options - Formulae - Enable Iterative Calculation - Maximum Iterations = 1.
In B3 enter formula =IF(B1>B2,B1,B3).

Seems there is an iterative calculation option in LibreOffice - see https://help.libreoffice.org/Common/Calculate#Iterative_references

John_Gray

LibreOffice Calc was written to match Excel as closely as possible. This would include dealing with circular references.