Libreoffice speadsheet help

lindabea

HI Guys, need some help with spreadsheet formula please. I want to put a value in B3, IF (B1>B2,B1). But here's the problem... works fine if B1>B2, but if the condition is false, I get 0 in B3. What I want is if the condition is false, B3 should not be updated. Any clever ideas please

fenlander_uk

Maybe: =IF(B1>B2,B1,"")

lindabea

fenlander_uk wrote: »

Maybe: =IF(B1>B2,B1,"")

Thanks for your suggestion, but it inserts a blank instead of zero. Back to the board

Paul_DNAP

What do you want B3 to show instead of the zero or the blank?

mksysb

There can't have been anything originally in B3 if you are replacing it with the formula. What are you expecting to be there? May be you could do something like
=IF(B1>B2,B1,"Value you expect")

Paul_DNAP

Or do you mean "There is already a value entered manually in B3, but you want it to be replaced by the value in B1 if B1 is greater than B2, but to remain with the value that you entered if B1 is equal or less than B2" - then this is indeed a lot more difficult.
That's because any formula you enter in there to do the B1<B2 will replace what is entered/typed in there already, you could do it if you're clever with macros and scripting.


I would suggest bringing B4 into play to be the results cell. B4 being =IF(B1>B2,B1,B3) that will leave all the original data untouched and still display the result you want (if indeed that is what you were wanting).

Cornucopia

I suspect that what the OP wants to do is impossible without scripting.

The use of formulas in spreadsheets works in a fundamentally different way to that imagined in the requirement expressed.

The content of B3 IS the formula concerned. What is seen is the evaluation of that formula. There is no "previous content" of the cell, it has gone as soon as the formula has been placed there.

lindabea

Paul_DNAP wrote: »

What do you want B3 to show instead of the zero or the blank?

I want B3 to show B1 - but only if B1 is >B2. B3 may already contain a previous value when the condition was true.

lindabea

Paul_DNAP wrote: »

Or do you mean "There is already a value entered manually in B3, but you want it to be replaced by the value in B1 if B1 is greater than B2, but to remain with the value that you entered if B1 is equal or less than B2" - then this is indeed a lot more difficult.
That's because any formula you enter in there to do the B1<B2 will replace what is entered/typed in there already, you could do it if you're clever with macros and scripting.


I would suggest bringing B4 into play to be the results cell. B4 being =IF(B1>B2,B1,B3) that will leave all the original data untouched and still display the result you want (if indeed that is what you were wanting).

You're spot on about what I'm trying to do, but my knowledge does not stretch to using macros. I don't quite understand how using B4 as you suggest overcomes the problem. What happens when I need to change the value in B1 and is > B2. I want the new greater value in B1 retained in B3. Conversely, if I enter a lwoer value in B1, I do not want the previous value in B3 to change or indeed go to zero or blank.

I'm not sure if using B4 solves the problem

Cornucopia

The IF function does not work in that way (cannot work in that way).

=IF(condition,X,Y)

The value placed in the cell will be X when the condition is true, and Y if the condition is false. The previous value of the cell is lost as soon as the formula is placed there.

Paul_DNAP has it right, I think, in that you will need a fourth cell to hold the preserved value.

You can try it the opposite way around to what he has suggested, and preserve the old value in B4, and then use:-

=IF(B1>B2,B1,B4) in cell B3.

But B4 will have to be the actual value that was previously in B3. It cannot be =B3, because that would create a circular reference.

WaywardDriver

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