Conditional Math

Quick reference

• 00:04 This video focuses on taking two topics and and breeding them together.
• 00:08 The first one is conditional logic and the second one is math.
• 00:11 And there's some specific formulas that we can use to actually sum or
• 00:15 count numbers or text where certain conditions are met.
• 00:19 So it's kinda getting a little bit more technical,
• 00:21 a little bit more hybrid, but here's the first concept here.
• 00:24 What we'd like to do is we'd like to try and work out what the salary cap is for
• 00:29 the players in the list on the left hand side here,
• 00:32 where the jersey number equals number 14.
• 00:35 So, in theory, we should be coming back with Alexandre Burrows.
• 00:38 Now, the way that we do this is we're not gonna start with an IF function.
• 00:42 What we're gonna do is we're gonna start with a SUMIF function.
• 00:45 This is a special function and it takes a couple of different things.
• 00:48 It says what's the range, what's the criteria and
• 00:50 what's the sum range that you'd like to deal with?
• 00:54 So, the range that we're gonna do is we're gonna look at the jersey number.
• 00:59 And then it says, all right, well,
• 01:01 what's the criteria in that column that you're looking for?
• 01:04 I'm gonna say, you know what?
• 01:05 I'd like to find where the Jersey number equals 14.
• 01:09 And it says, fair enough.
• 01:11 If you find a Jersey number that equals 14,
• 01:13 what is the offsetting range that I can look in to return the same row?
• 01:18 So it's gonna look down through every row in A4 to A11, try and
• 01:24 find 14 and when it finds, it's gonna take that exact same row from another area.
• 01:29 And for that reason, these areas have to be the same size, okay?
• 01:32 Same number of rows.
• 01:34 So when we do that, it comes back and
• 01:36 it says, $2 million, which is Alexandre Burrows, that's perfect.
• 01:41 What if we want to go though and say, geez I'd like to see the salary where
• 01:46 the jersey number is greater than or equal to 14.
• 01:48 Well, it's a similar process with a small difference.
• 01:51 We'll say equals SUMIF, we'll still grab the column of jersey numbers.
• 01:56 But in the criteria, what we need to do is we actually need to put some quotes around
• 02:00 and say greater than or equal to and the jersey number.
• 02:07 This has to be text, the greater than or equal to.
• 02:09 So I'm just depending the Jersey number two at.
• 02:12 And then we'll say, when we find that record over here, greater than or
• 02:16 equal to, let's find any offsetting record from the same size range.
• 02:19 Close the brackets.
• 02:21 And we'll hit enter.
• 02:23 And you can see that it picks up 22.45 million.
• 02:25 And if you actually go back and count all these guys off,
• 02:29 what you'll find is that where those ones actually works, so
• 02:32 in Jersey is any number greater than or equal to 14, if I highlight them
• 02:36 you can see the sum total down on the bottom here matches what we've got.
• 02:39 So that's kind of nice.
• 02:42 What if we want to count numbers or
• 02:44 count records where the Jersey Number equals this?
• 02:46 Well, that's not a problem.
• 02:47 We can use COUNTIF.
• 02:49 Okay, so slightly different one.
• 02:51 And we'll just grab this particular column.
• 02:53 And it says,
• 02:54 I don't need to know an alternate range here because I'm just counting.
• 02:57 So what is your criteria?
• 02:59 Well, I'm gonna say, quote greater than, and the value 17.
• 03:04 So, count all the number of jerseys that are higher than 17.
• 03:08 And we said, remember, greater than this time, not equals.
• 03:12 So, in this case here, 78 we have as the total.
• 03:16 You've got three of them and there they are.
• 03:17 And the cool thing here, of course, is that I could now modify this and
• 03:21 it's gonna come back with four jerseys.
• 03:23 So that's why we use these formulas, is that they're dynamic.
• 03:27 What about where jersey number is less than or equal to five?
• 03:30 Again, we'll go COUNTIF, and
• 03:33 we'll grab the jersey numbers, we'll say alright, let's go with less than or equal,
• 03:39 and, the cell value we want, close our brackets.
• 03:45 And it's found three jerseys where it's less than or equal to 5.
• 03:47 And that will be these three right here.
• 03:49 You can see the rest of the numbers are higher.
• 03:52 Now, what if we want to get into really complex stuff where there's actually three
• 03:55 criteria that we wanna look at?
• 03:57 This one definitely gets a little bit more advanced, but
• 04:00 there's a function specific for this as well, called SUMIFS, and that's a plural.
• 04:05 So where we used SUMIF above,
• 04:07 SUMIFS allows us to provide multiple criteria ranges and criteria.
• 04:12 What's different though, is that in SUMIF we provided the criteria range first and
• 04:17 then the range we wanted to sum afterwards.
• 04:20 In SUMIFS, plural, we actually start with the range we want to sum.
• 04:23 So we're gonna start with the Cap Hit column.
• 04:26 And what we're gonna do now is say well what's the criteria
• 04:28 range we're gonna look at?
• 04:30 Well I'm going to look in the Jersey Number.
• 04:34 And then what's the criteria?
• 04:36 I'm gonna say we're gonna go less than or equal to and Jersey number 23.
• 04:43 And then it says what's the next criteria range that you're gonna look at?
• 04:47 We're again looking at jersey numbers for greater than or equal to, so
• 04:52 I'm gonna select that range again.
• 04:54 And I'm gonna say comma, and this time we're gonna go greater than or
• 04:58 equal to 14.
• 04:59 Oops.
• 05:01 Missed that one, let's go back here.
• 05:03 I missed putting in my and character, that's important.
• 05:06 Now we can pick it up.
• 05:07 There we are. We're gonna do one more,
• 05:11 we'll say years remaining.
• 05:13 So we'll go and say, let's go with the Years Remaining column and
• 05:17 the criteria is gonna be less than or equal to.
• 05:20 And the value that's showing up here.
• 05:25 When we hit close on this you can see we can put as many of these as we want in.
• 05:28 When I hit enter it gives me a nice number.
• 05:30 And if you were to evaluate these all backwards and
• 05:32 forwards, it comes up to this.
• 05:34 And then, of course, I could end up playing around and
• 05:36 say you know, tell me how players have contracts that are expiring in four years.
• 05:41 Maybe I want to go and see only jersey numbers where they're greater than or
• 05:45 equal to 23 and less than or equal to 23.
• 05:48 That's gonna drill me directly into Alexander Edler.
• 05:52 He has four years remaining on his contract, his jersey number works, and
• 05:56 you can see the value is working nicely.

Lesson notes are only available for subscribers.