Consulting

Results 1 to 11 of 11

Thread: Update Another Sheet with Results of Formula w/Multiple Criteria

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    13
    Location

    Update Another Sheet with Results of Formula w/Multiple Criteria

    I'm trying to institute a formula that will calculate the difference between some rows. The sticky part is, I need to find the difference from the greatest update date (which will always be at the bottom of the set) from the smallest update date (which will always be at the top of the set.

    Type Entry Type Weight Weight Change Expectation
    Cat Initial 110.00 0.00 0.00
    Cat Update 105.00 -5.00 -5.00
    Cat Update 102.00 -3.00 -8.00
    Dog Initial 175.00 0.00 0.00
    Dog Update 172.00 -3.00 -3.00
    Dog Update 170.00 -2.00 -5.00

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,013
    Location
    the difference from the greatest update date (which will always be at the bottom of the set) from the smallest update date
    How do we calculate the time between two dates when there are no dates shown?
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Oct 2017
    Posts
    13
    Location
    Sorry about that. I thought I put that column in there.
    Type Entry Type Weight Weight Change Expectation Update
    Cat Initial 110.00 0.00 0.00 1/1/18
    Cat Update 105.00 -5.00 -5.00 1/15/18
    Cat Update 102.00 -3.00 -8.00 1/22/18
    Dog Initial 175.00 0.00 0.00 12/1/17
    Dog Update 172.00 -3.00 -3.00 12/26/17
    Dog Update 170.00 -2.00 -5.00 1/12/18

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,666
    Don't know the addresses of the cells where your data are, so something along the lines of:
    =MAX(IF(($A$2:$A$7=A2)*($B$2:$B$7="Update ")=1,$F$2:$F$7))-MIN(IF(($A$2:$A$7=A2)*($B$2:$B$7="Update ")=1,$F$2:$F$7))
    or:
    =MAX(IF(--($B$2:$B$7="Update ")=1,$F$2:$F$7))-MIN(IF(--($B$2:$B$7="Update ")=1,$F$2:$F$7))
    bothe entered with Ctrl+Shift+Enter, not just Enter
    Which formula depends on whether you want dates per Type or not.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Oct 2017
    Posts
    13
    Location
    Thanks for the response! I thought I responded to your post earlier, but apparently it didn't go through.

    I don't believe that I explained my issue very well in my original post. What I'm trying to do is calculate the weight change between the Initial Update and the most recent Update. I've added column headers, thinking that it may be easier to explain and understand. I'm not sure if it's easier to use the the value in column F or H. The last "column" in the table below is just showing what the expected results are.

    For full transparency, the end goal is to enter the formula into the worksheet via a macro. Additionally, there is another sheet that will look to get updates from this particular sheet, where the Type is the same on both sheets. My thought process is to figure out the formula, then I should be able to code it into VBA, largely via the macro recorder. Between that method and forums like this one, I'm trying to teach myself VBA.

    C F H J K
    Type Entry Type Update Weight Weight Change Expectation
    Cat Initial 1/1/18 110 0 0
    Cat Update 1/15/18 105 -5 -5
    Cat Update 1/22/18 102 -3 -8
    Dog Initial 12/1/17 175 0 0
    Dog Update 12/26/17 172 -3 -3
    Dog Update 1/12/18 170 -2 -5

    Again, thank you for the reply.

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,666
    See attached.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Oct 2017
    Posts
    13
    Location
    Thank you for the file! I've never seen a SUMIF structured quite like that, and the file made the formula much easier to understand. However, while close, it's not quite what I was looking for. I inserted a row on the attached (in yellow), that shows where the weight increased by 3. I was hoping the formula would identify the max difference in the weight column.
    Attached Files Attached Files

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,666
    See attached.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Regular
    Joined
    Oct 2017
    Posts
    13
    Location
    So far, this looks great! Thank you! I do need to add some more data and continue testing, but I want to make sure that I understand the formula, so I can try and apply it in future endeavors. Can you help me understand what the * is doing? I'm used to that being either a multiplication indicator or a wildcard.

  10. #10
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,666
    It's multiplying.
    If you take them separately you have two arrays of TRUE/FALSE:
    2018-01-19_154011.jpg
    When you multiply them (TRUE * TRUE = 1, all the rest TRUE * FASLE, FALSE * TRUE and FALSE * FALSE, all = 0):
    2018-01-19_154351.jpg
    So I get an array of noughts and ones.
    I then put that array in an IF statement saying if a member is equal to one, give the weight, otherwise return FALSE (I want FALSE because I'm going to apply a MIN function to it, and that function ignores boolean values.).
    2018-01-19_155031.jpg
    Applying the MIN function returns the lowest weight up to and including the date on that row for that Type.

    That's the first half of the equation. Now we need to subtract the weight on the 'Initial' date, for the same Type, which is what the second half of the equation gives. The MIN in the second half isn't really necessary; there should only be one row like that. But if there were more than one row like that, this formula would take the weight from the lowest of them.
    Last edited by p45cal; 01-19-2018 at 11:56 AM.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Regular
    Joined
    Oct 2017
    Posts
    13
    Location
    That's some really neat stuff! Thank you!!

Posting Permissions

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