Results 1 to 9 of 9

Thread: Tracking Readings for Parameter and Marking Red if Different Than Previous Reading -

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    4

    Tracking Readings for Parameter and Marking Red if Different Than Previous Reading -

    Struggling with this Excel (likely VB) problem and appreciate any insight/ideas…

    Need to build solution that checks readings by parameter after entered into spreadsheet and change font color to red if difference between new reading and previous reading is >=Diff check or <=DiffCheck.
    Reading 1 will always be initial reading. We start evaluating each param w reading 2.

    Sample data is in attached for reference.

    Example for Param 1:
    Reading 1 = 4.663
    When reading 2 is entered, 4.706, Excel needs to check difference (diff check table shows thresholds) between reading 1 and reading 2. In the scenario of Param 1, if reading 2 is 5% larger or smaller than reading 1 then reading 2 font color would change to red.

    However, when evaluating reading 3, we need it to check diff vs. reading 1. Next check would be reading 4 vs reading 2 then 5 vs 3, 6 vs 4, 7 vs. 5, and finally 8 vs. 6.

    Parameter 1 has a value for each subsequent reading after 1. However, this is not always the case.

    As stated, starting with reading 3, we are checking diffs vs. reading entered 2 readings above (3 vs. 5).

    In the scenario a “-“ is entered and not a reading when checking above, the diff calc should take place against last entered reading.

    If you take a look at Param 2, we didn’t have a reading after 1 until 5. Therefore, we need to check diff of 5 vs. 1.

    Param 2 also as 2 diff check scenarios, 10% larger or smaller, or 1 unit increase or decrease since last reading.
    Finally, when a “-“ is entered, this should change to red font as well (my peon brain can handle this).

    The rest of it, however, is a new challenge. If anyone could please provide some feedback on how you would approach such scenario so at least I could get started with one, I’d be so gracious.

    Thank you!
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    from what i am reading, if you want to compare current readings to previous readings, it would appear you have to store multiple versions of previous readings?
    would this be on a short term or session basis or a more permanent basis?
    what type of data volume would you be working with? i would assume a much larger amount of data than in the sample
    can you use trackchanges to help with what you want to do
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    4

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    Thanks for the reply.

    On road but will post word file with example soon.

    It’s a small amount of data with complicated requirements, unfortunately, doing an important job.

    Outcome will be applied to a template with potentially multiple sheets (1-2) using the same template.

    Each parameter will only have 8 reads total. If you check out attached excel in zip my summary should hopefully sync up. In hindsight, I could’ve summarized what I’m looking to do much better. Many apologies.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    4

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    Here is what the table would look like:

    Name:  rp - Copy.jpg
Views: 114
Size:  22.3 KB

    I don't think there's anything out of the box to address this. Appreciate anyone's insight who has dealt with something similar!

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    Well, start at the beginning.
    Get the first reading.
    Compare the row-Number you've entered reading 1 to the last entered row-number (in case of reading 1 they are the same --> Don't do anything)
    Get next reading.
    Compare the row-number you've entered this reading into with the row-number fo reading 1.
    If difference is smaller than 2, it's the second reading. Compare it with the previous row. Apply Diff-Calculation
    If difference is equal or greater than 2, then it's reading 3 or higher.
    Compare row-number of this reading with Current-Rownumber - 2. Apply Diff-Calculation
    and so on.
    if Reading of (CurrentRow - 2) is "-" then step one row up, check against "-", if again, step one more up and so on.

    As for your Diff-Calculation: What's the 100%-base? Your current row, or the one you're checking against?
    100 USD <-> 110 USD --> The second is 10% higher than the first (left being 100%-base), the first is 9.09 % lower than the second (the second being 100%-base).
    If the row you're checking against is the 100%-base, then it goes like this:

    First Check: If "-" Then CurrentRow.ColorMeRed (This should be the first check)
    Param1 (the same for Param3): If 100*(CurrentRow.Param1/CheckRow.Param1)-100<-1*DiffParam1 or CurrentRow.Param1/CheckRow.Param1>DiffParam1 then CurrentRow.ColorMeRed
    Param2: If (100*(CurrentRow.Param2/CheckRow.Param2)-100<-1*DiffParam2 or CurrentRow.Param2/CheckRow.Param2>DiffParam2) OR (CurrentRow.Param2-CheckRow.Param2<-1*DiffParam2+/- or CurrentRow.Param2-CheckRow.Param2>DiffParam2+/-) then CurrentRow.ColorMeRed

    This is all Aircode, but that's the way i would approach it
    Last edited by Zvoni; Apr 11th, 2019 at 01:14 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    4

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    Quote Originally Posted by Zvoni View Post
    As for your Diff-Calculation: What's the 100%-base? Your current row, or the one you're checking against?
    Thanks for the reply, Zvoni! 100% base is the one you are checking against. I'm back into a technical role and haven't messed with VB in almost 5 years at least.

    I know if I can figure out where to start with actual VB I can finish rest but honestly I'm rusty.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    are the tables updated manually by the user or automatically by incoming data?

    the picture which is the same as in the worksheet really tell us nothing, but as there is only one table, we must assume that the updated values overwrite the previous values

    you need to store all previous value that may need to be compared against, in either short term or long term memory, you can do this into arrays for short term storage, or separate tables on your worksheet or additional worksheets, but you need to give some clues what would best work for your project
    if the tables are updated automatically, you would probably need to use a timer to poll for when the data is updated, if the updating is done by the user, then the worksheet change event can be used

    doing the calculations is probably the easiest part, once the method to do so is set up
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    I think he was looking for advice how to even start with an algorithm.
    Once he has the algorithm down, it's the question where to put it, depending, as you described, how the data is coming in
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Tracking Readings for Parameter and Marking Red if Different Than Previous Readin

    lol
    i looked at it the other way if i could work out where the data(s) to compare would be, i would figure the math later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width