SUMPRODUCT Part 2

Quick reference

• 00:04 I'm going to go straight back to another SUMPRODUCT example.
• 00:07 Make it a little bit more advanced, and show you just why SUMPRODUCT is so
• 00:13 useful when analyzing data and modeling.
• 00:18 It's such a powerful function, more now.
• 00:23 You may recall last time,
• 00:24 I was looking at contrasting the SUMIFS example with its SUMPRODUCT variant.
• 00:30 The original SUMIFS solution was to actually use this formula.
• 00:37 What I don't like about this is you don't know what's for range and
• 00:40 what's for criteria necessarily.
• 00:41 And it becomes you suddenly have to keep alternating between a range and
• 00:44 just a cell, range and a cell.
• 00:46 Whereas with the SUMPRODUCT version,
• 00:50 I think this is a little more intuitive even if you don't follow it.
• 00:54 Because if you understood the idea of flags,
• 00:56 you realize that we're evaluating truths and falses in each bracket.
• 01:00 Which is why we're putting them in brackets.
• 01:01 So we can multiply them together to convert them to ones and zeros,
• 01:05 back to the idea of boolean algebra.
• 01:08 One thing you've got to watch out for though, which I didn't show you last time,
• 01:11 is here's something I prepared earlier.
• 01:14 Here's the same thing, but I've replaced the times with a comma.
• 01:18 And guess what?
• 01:19 It's not worked, because when it's evaluating a true,
• 01:24 if I'm not multiplying it, it's not numerical.
• 01:26 And remember what the slide said?
• 01:28 It's said that if it's not numeric, it's treated as zero.
• 01:32 So everything is seen as zero.
• 01:34 So you can't do that.
• 01:35 So you must convert the truths and falses into zeros.
• 01:39 Now an alternative way,
• 01:40 if you must use comas, is to put a minus minus in front of each.
• 01:46 My favorite noise, isn't it, in this course?
• 01:49 The first minus turns to into a number, but negates it.
• 01:53 The second minus makes it positive again.
• 01:56 Now it'll work.
• 01:58 Now studies have shown apparently,
• 01:59 this is the most efficient way to calculate a SUMPRODUCT function.
• 02:03 But it's not the most useful.
• 02:06 So minus the first one turns it into a number but it's negative.
• 02:09 The second negative sign turns it back.
• 02:12 And apparently that's faster than multiplying by one,
• 02:14 which could be the alternative for each one.
• 02:17 But I suggest you use times and in this last example I show you why.
• 02:23 Take a deep breath we're a bank.
• 02:26 We've got four accounts here, we've got transaction accounts,
• 02:30 savings account, credit card and mortgage.
• 02:32 And these two types is a standard of budget.
• 02:36 I'm the bank manager, and I look at my staff, and they make so
• 02:39 many appointments a week.
• 02:40 We have 80 for transaction accounts, 45 for savings, and so on.
• 02:45 Each account, well it has a preparation time.
• 02:48 It takes four hours of prep for a transaction account, three for savings.
• 02:52 It's got four hours of application prep, but it's actually got one and
• 02:57 a half hours on top of that.
• 02:59 For the processing time for transaction accounts are similar we've got the review
• 03:03 time of the further six hours per account.
• 03:06 We've also got materials of three hours per account.
• 03:10 And then, we've got these back office costs of $480 prep for an account.
• 03:14 And sometimes it costs to $250.
• 03:16 In case any bank is listening in, I have made all these numbers up on the spot.
• 03:23 Now we have turned down here is I actually put some more.
• 03:27 So for each of our application time costs $87 an hour.
• 03:30 For each processing time, a whooping $695 an hour.
• 03:35 Review time 19, material prep 23.
• 03:38 And just to complete it cuz these are already in dollars
• 03:41 I just put the number one.
• 03:43 Do you see what all there to do?
• 03:45 If I wanna work out the total costs, I've got to say,
• 03:50 well, I've got 80 of these, times 4, times 87, plus 80, times 1 1/2, times 685,
• 03:55 plus 80, times 6, times 19, plus 80, times 3, times 23".
• 04:00 Plus 80 times 480 times 1.
• 04:02 That's where I put the one in so I've got the pattern going.
• 04:05 Plus 80 times 150 times 1 plus 45 times 3 times 87.
• 04:10 Plus 45 We're going to be asleep before we know it.
• 04:13 Would you like to write that formula?
• 04:15 It's gonna take an ordinate amount of time.
• 04:18 And the chances are you probably miss something, double count, and
• 04:21 it will be wrong.
• 04:22 There's a much simpler way.
• 04:25 I'm just gonna use SUMPRODUCT, equals SUMPRODUCT open brackets, and
• 04:30 I'm going to put in here, okay, I've got these in my appointments.
• 04:35 I'm gonna make that absolute for.
• 04:39 Multiplied by the actual data here,
• 04:42 this table of all the hours that drop to make that absolute.
• 04:46 And then multiple it by these numbers here and make that absolute.
• 04:51 That's the answer, $612,660.
• 04:57 It's as simple as that, but I can break it down into budget and standard.
• 05:01 Which is why I make it absolute.
• 05:02 I'm going to copy this one across from and
• 05:05 what I'm going to do is I'm going to add times in open brackets.
• 05:11 These here made absolute, this is my condition, has to equal budget.
• 05:17 All 58718.
• 05:21 Copy it across, it will do it for standard and of course these two add up.
• 05:26 Can you see how how much time that saved?
• 05:31 This is a really useful function in Excel and
• 05:34 it's one you should take some time to master.

Lesson notes are only available for subscribers.