Results 1 to 5 of 5

Thread: Calc: did cell value increase or decrease

  1. #1
    Join Date
    Apr 2014
    Location
    "Great North Wet"
    Posts
    116

    Default Calc: did cell value increase or decrease

    Is it possible for some kind of formula to determine if a cell value increased or decreased without making a copy of the cell's value beforehand?
    I have a cell A1 that gets updated every 15 minutes by a 'link to external data' and I want to color another cell B1 based on if A1 increased (green) from last time or if A1 decreased (red) or if A1 stayed the same (black)
    Turkey Creek Jack Johnson: Nobody move!
    Doc Holliday: Nonsense. By all means, move.

  2. #2
    Join Date
    Jun 2008
    Location
    Podunk
    Posts
    26,123
    Blog Entries
    15

    Default Re: Calc: did cell value increase or decrease

    Hi
    Look at the conditional formating. Menu item: Format -> Conditional Formatting -> Manage.

    For example I have a cell with a formula (data);

    Code:
    =IF(F6<>"",(NOW()-F6),"N/A")
    This looks at F6 which is a date and compares to the date now in days.

    Then I have three conditions;

    Condition 1: If cell value is less than or equal to -20

    Style name Good: Text color green

    Condition 2: If the cell value is between -19 and 0;

    Style name Warning: Text color Orange

    Condition 3: If cell value is > 0

    Style name Bad: Text color red and bold

    So you just add your conditions, then in the drop down you can select pre-existing formats, or in my case created what I wanted.

    In your case your going to have to store/stash the previous cell value somehow...?
    Cheers Malcolm °¿° SUSE Knowledge Partner (Linux Counter #276890)
    SUSE SLE, openSUSE Leap/Tumbleweed (x86_64) | GNOME DE
    If you find this post helpful and are logged into the web interface,
    please show your appreciation and click on the star below... Thanks!

  3. #3
    Join Date
    Apr 2014
    Location
    "Great North Wet"
    Posts
    116

    Default Re: Calc: did cell value increase or decrease

    That's the problem, in your case DATE is a naturally changing value so each time you read it its different. In my case i have a financial spreadsheet and I ant to know if a cell increased or decreased from the last time it was updated.
    As for making a copy, I'm going to go out on a limb and say it cant be done. What i mean is, sure you can manually copy it over someplace if you want to sit in front of your computer and do that every 15+1 minutes or try to setup some kind of timer to call a fn every x minutes, but that's ify and Calc may not even have timers. Calc should have the old value of A1 after it updates A1 because i think you can Ctrl-Z to go back to it (maybe not.) I cant think of a way to set up a formula to activate on or just before the 'link to external data' updates
    Turkey Creek Jack Johnson: Nobody move!
    Doc Holliday: Nonsense. By all means, move.

  4. #4
    Join Date
    Jun 2008
    Location
    Podunk
    Posts
    26,123
    Blog Entries
    15

    Default Re: Calc: did cell value increase or decrease

    On Wed 22 Nov 2017 06:46:01 PM CST, erbenton wrote:

    That's the problem, in your case DATE is a naturally changing value so
    each time you read it its different. In my case i have a financial
    spreadsheet and I ant to know if a cell increased or decreased from the
    last time it was updated.
    As for making a copy, I'm going to go out on a limb and say it cant be
    done. What i mean is, sure you can manually copy it over someplace if
    you want to sit in front of your computer and do that every 15+1
    minutes or try to setup some kind of timer to call a fn every x minutes,
    but that's ify and Calc may not even have timers. Calc should have the
    old value of A1 after it updates A1 because i think you can Ctrl-Z to go
    back to it (maybe not.) I cant think of a way to set up a formula to
    activate on or just before the 'link to external data' updates


    Hi
    OK, then probably a macro with a listener is needed... if you right
    click on the sheet tab and select sheet events there is one there called
    "Content Changed" have a browse of existing ones to see if there is one
    that fits the bill...

    --
    Cheers Malcolm °¿° SUSE Knowledge Partner (Linux Counter #276890)
    openSUSE Leap 42.2|GNOME 3.20.2|4.4.92-18.36-default
    If you find this post helpful and are logged into the web interface,
    please show your appreciation and click on the star below... Thanks!


  5. #5
    Join Date
    Apr 2014
    Location
    "Great North Wet"
    Posts
    116

    Default Re: Calc: did cell value increase or decrease

    Thanks, I wasnt aware of those listeners at all. I'll check them out in the next few days and report back
    Turkey Creek Jack Johnson: Nobody move!
    Doc Holliday: Nonsense. By all means, move.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •