+ Reply to Thread
Results 1 to 10 of 10

Dynamic "highest score" formula

  1. #1
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Question Dynamic "highest score" formula

    Hello people, I'd like to check up on something that I think that I already know the answer, but I wanna at least have some confirmation before I can eventually abandon my hopes and move forward.

    Here's the situation: I want to come up with a formula that can automatically set the "highest score" that was ever found in a sheet. How's that supposed to work? Well, let's say that, for instance, there's that well known counter of how many days a certain business has gone through without having any kind of accident / incident, right? Ok, until that part we're game, the counter works just fine with the Max function, always subtracting the present day from the latest date of occurence. The thing is that I want to create a criteria to estabilish a parameter to be surpassed, like "The best score so far was 60 days", so when you reach 61, that number is changed automatically and a new record is therefore estabilished.

    I'm pretty sure that it's definitely possible to achieve that through VBA, but before I start researching how to get that kind of result, I'd first like to know if that could ever be achieved through the regular Excel formulas.

    By the way, thank you for taking your time to read through all this,


    Zenarte

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "highest score" formula

    Hi,

    It's not clear to me what you're getting at and what 'subtracting the present day from the latest date occurrence' actually means in practice and how it impinges on your calculations.

    It's always better if you upload the workbook in which you've manually added your results.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Re: Dynamic "highest score" formula

    Hello Richard, thank you for your reply. Ok, I've made an upload of what I'm looking forward, I'll try to explain what I'm intending to achieve:

    https://mega.nz/#!E5oCDLJB!qWRrQoucb...usqWXpr4-GCpMk



    In that sheet, the current "high score" would be of 158 days. Either way, let's assume that there was an incident today. That would make the counter return to zero again, right? Ok, what I want to happen is for B3 to automatically keep track of the highest value that was ever achieved, in other words, 158 days. If that record is broken eventually, it'll change accordingly again.

    Thanks again for your time and patience,


    --

    Zenarte

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "highest score" formula

    Hi,

    Would you upload the workbook to this forum please. Many of us prefer not to visit file hosting sites of unknown provenance.

  5. #5
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Re: Dynamic "highest score" formula

    Hello again, I've tried to upload with the icon on the hud but that didn't work. Then I've scrolled down the screen and I found that you could upload files through the "manage attachments". I always forget about that detail.

    Ok, file attached ~


    --

    Zenarte
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "highest score" formula

    You'll need to explain your terminology. Remember that this is meat and drink to you, we have no idea what your system is doing or how it works so you need to spell it out.

    What causes the high score counter to change and return to zero? Where is the data that causes it to change and what do you mean by 'if that record is broken eventually' and how would that happen.

    There are only 3 cells in your workbook so I'm struggling to understand how the thing works.

  7. #7
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Re: Dynamic "highest score" formula

    If you insert today's date at B1, the value in B2 will return to zero, right? In other words, the "successful streak" will be interrupted. What I want, is for B3 to keep track of the highest value that could ever be achieved.

    So, let's say that time passes by and the present record is still 158 days. All of a sudden, we get to 159 days. Ok, in that case, B3 would automatically become 159 days, because that would be the new record.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Dynamic "highest score" formula

    If I understand what you want, a simple circular reference (no VBA) in B3 would do this. =MAX(B2,B3). See discussions here https://www.excelforum.com/excel-pro...-possible.html and here https://www.excelforum.com/excel-for...ging-cell.html

    Note that I would probably put a little more effort into this, mostly to give me a way to "reset" the loop, but the basic idea is there.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Re: Dynamic "highest score" formula

    Thank you for the feedback, MrShorty. I'll check out that topic and see if I can make it work

  10. #10
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Re: Dynamic "highest score" formula

    @MrShorty, your suggestion worked perfectly, thank you very much for it! For now I'm just working with the simple circular reference, I didn't really get the "reset the loop" concept, but I'll read more until I figure it out.

    @Richard, thank you for taking your time to try to help me too, I really appreciate that

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-09-2016, 12:17 PM
  2. Formula To "Score" Similar Data Entries
    By cmf0106 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2013, 11:46 AM
  3. Replies: 1
    Last Post: 06-21-2012, 05:19 AM
  4. How to change "From" & to have a dynamic "Subject" line in Excel Mailing
    By andy_iyeng in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2012, 03:04 AM
  5. Replies: 2
    Last Post: 03-20-2012, 06:30 PM
  6. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  7. [SOLVED] If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1