Update Another Sheet with Results of Formula w/Multiple Criteria

[Rodger]

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

[SamT]

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?

[Rodger]

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

[p45cal]

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.

[Rodger]

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.

[p45cal]

See attached.

[Rodger]

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.

[p45cal]

See attached.

[Rodger]

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.

[p45cal]

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.

[Rodger]

That's some really neat stuff! Thank you!!