Static vs. dynamic maximum

classic Classic list List threaded Threaded
7 messages Options
Chimel Chimel
Reply | Threaded
Open this post in threaded view
|

Static vs. dynamic maximum

Hi,

I must be of a conservative mind today, because after the message about
static NOW(), I am also stuck with extracting the maximum value of one given
cell.
For instance, A1 contains variable numbers, and I want to store the maximal
value of this cell in B1.
The trouble is that MAX() compares 2 different cells, whereas I want the
maximal value of one cell.
A recursive formula such as =MAX(A1;B1) would probably not work, and returns
an error 523 indeed.

Is there a way to store the maximal value of a cell using a formula,
preferably?

Thanks,
Chimel.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: Static vs. dynamic maximum

On Fri, 2 Oct 2020 06:41:27 -0700 (MST)
Chimel <[hidden email]> wrote:

> Hi,
>
> I must be of a conservative mind today, because after the message
> about static NOW(), I am also stuck with extracting the maximum value
> of one given cell.

What do you mean by 'the maximum value' of a cell?

It sounds like you think cells have a history of their contents?
That's not the case. A cell has a value. If you then enter a different
value in the cell, it has the new value. It does not 'remember' the old
value. (Notwithstanding the ability of the UI to 'undo' editing -
that's something completely different).

So the entire notion of the 'maximum' value of a cell is meaningless.

> For instance, A1 contains variable numbers, and I want to store the
> maximal value of this cell in B1.
> The trouble is that MAX() compares 2 different cells, whereas I want
> the maximal value of one cell.
> A recursive formula such as =MAX(A1;B1) would probably not work, and
> returns an error 523 indeed.
>
> Is there a way to store the maximal value of a cell using a formula,
> preferably?
>
> Thanks,
> Chimel.

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Chimel Chimel
Reply | Threaded
Open this post in threaded view
|

Re: Static vs. dynamic maximum

A cell with a formula can have a whole "history" of values that change every
now and then.
It has only one value at one given time, it's this value that I want to
compare with its previous values so I can extract the maximal value the cell
ever reached.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Brad Rogers Brad Rogers
Reply | Threaded
Open this post in threaded view
|

Re: Static vs. dynamic maximum

In reply to this post by Dave Howorth
On Fri, 2 Oct 2020 15:03:17 +0100
Dave Howorth <[hidden email]> wrote:

Hello Dave,

>So the entire notion of the 'maximum' value of a cell is meaningless.

I /think/ what Chimel wants is

 B1=A1 if A1>B1

How to achieve that in LO Calc, IDK;  I keep getting circular reference
errors.

--
 Regards  _
         / )           "The blindingly obvious is
        / _)rad        never immediately apparent"
Just coz they do it in the movies, doesn't mean to say that it's cool
Keep It Clean - The Vibrators

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Static vs. dynamic maximum

In reply to this post by Chimel
At 06:41 02/10/2020 -0700, Chimel Noname wrote:
>... I am also stuck with extracting the maximum value of one given
>cell. For instance, A1 contains variable numbers, and I want to
>store the maximal value of this cell in B1.

As others have said, the cell, does not "contain variable numbers",
but only the single value (or expression) that is there at the time.

>The trouble is that MAX() compares 2 different cells, whereas I want
>the maximal value of one cell.

No, you don't.

>A recursive formula such as =MAX(A1;B1) would probably not work, and
>returns an error 523 indeed.

Actually, this works: read on.

At 07:18 02/10/2020 -0700, Chimel Noname wrote:
>A cell with a formula can have a whole "history" of values that
>change every now and then.

No, the cell has just whatever is in it now.

>It has only one value at one given time, it's this value that I want
>to compare with its previous values so I can extract the maximal
>value the cell ever reached.

But the previous values have been overwritten by the current one, so
are no longer there.

Actually, you have solved your own problem. If you want to keep a
record in B1 of the largest of all the values that have appeared in
A1, then putting
=MAX(A1;B1)
into B1 would appear to work - as B1 has kept a record of that part
of the history of A1's values that you need. But the logic is
recursive: evaluating the formula changes one of its parameters, so
the formula potentially needs evaluating again. How many times should
this happen? The process might never terminate. In this case, you
want the calculation to happen once only, but how can LibreOffice know that?

All you need to do is to permit recursive calculations, which you do
by ticking Tools | Options... | LibreOffice Calc | Calculate |
Iterative References | Iterations. Then your formula works.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Chimel Chimel
Reply | Threaded
Open this post in threaded view
|

Re: Static vs. dynamic maximum

Thanks, Brian. The formula I mentioned earlier works now.
I tried the Iterations settings before, enabled and disabled it with
different values (step, min), but when I clicked on the Reset button to
restore the default options, it actually only restores the previous values I
played with before, not the LibreOffice "factory settings". These previous
values of 1 for steps and min return the error 523 I mentioned.

I tried step=100 and min=0,001, and my formula (the same you mentioned)
works just fine.
So the formula was fine all along, it's the Iterations settings that were
incorrect.
Problem solved, thanks to everybody who helped and sorry if I was not clear.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Static vs. dynamic maximum

In reply to this post by Chimel
Den fre 2 okt. 2020 kl 16:20 skrev Chimel <[hidden email]>:

> A cell with a formula can have a whole "history" of values that change
> every
> now and then.
>

I'm pretty sure it can not. Please prove me wrong. I'd love to know what
this is all about. Maybe I just don't understand what you are trying to say.


Kind regards

Johnny Rosenberg


> It has only one value at one given time, it's this value that I want to
> compare with its previous values so I can extract the maximal value the
> cell
> ever reached.
>




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy