PDA

View Full Version : Update Another Sheet with Results of Formula w/Multiple Criteria



Rodger
01-15-2018, 07:28 PM
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
01-15-2018, 07:46 PM
the difference from the greatest update date (which will always be at the bottom of the set) from the smallest update dateHow do we calculate the time between two dates when there are no dates shown?

Rodger
01-15-2018, 08:30 PM
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
01-16-2018, 04:47 AM
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
01-16-2018, 05:40 PM
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
01-17-2018, 02:46 AM
See attached.

Rodger
01-17-2018, 03:56 PM
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
01-18-2018, 05:32 AM
See attached.

Rodger
01-18-2018, 12:25 PM
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
01-19-2018, 09:02 AM
It's multiplying.
If you take them separately you have two arrays of TRUE/FALSE:
21403
When you multiply them (TRUE * TRUE = 1, all the rest TRUE * FASLE, FALSE * TRUE and FALSE * FALSE, all = 0):
21404
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.).
21405
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
01-19-2018, 11:24 AM
That's some really neat stuff! Thank you!!