# Dynamic "highest score" formula

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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

6. ## 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. ## 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. ## 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.

9. ## 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. ## 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

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

#### 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