The Levo League

Posted on Tuesday January 31st 2012 at 12:36pm. Its tags are listed below.

Technical Skill Tuesdays: Meet f(x)=Sumproduct ! As we mentioned last week, sharing is caring in the Excel universe. Learn a trick to teach to a coworker, and you’re suddenly the go-to for Excel wisdom. If that’s a burden you’re interested in bearing, this segment is for you!Let’s set the stage for this week’s Excel scenario: You are planning an event for your University Alumni Association. Non-members have to pay a premium to attend the event, members pay slightly less, and recent grads pay the least. The event coordinator asks you to create scenarios showing how many people in each group you need in order to cover the cost of the event. You set up a row for each type of attendee and include both the cost of the ticket by attendee type and the number of projected attendees in two separate columns. By multiplying these two columns and summing the three rows’ totals together, you know how much revenue the members will contribute to the cost of the event. How do you do it all in one fell swoop? I’m glad you asked! As it turns out, there’s a nifty little function in Excel called “Sumproduct.” A very easy way to understand this is to think about the “Sum of Products” - Microsoft probably could have been more intuitive in the way they named it, but we’re talking about engineers in an office environment famed for its cheap pizza, on-site showers, and annual paintball-a-thon. We’ll forgive them for being slightly inaccessible to the rest of humanity.When is Sumproduct actually worth using?Since we only set up three different pricing tiers in this model, it isn’t THAT laborious to manually multiply and then add. But, imagine if you had 15 different attendee types. Or worse, 150! It’s at this point of calculation that Sumproduct really hits its stride.To “sum” today’s TST up (no groans, please): Instead of multiplying each row, and then adding them together, Sumproduct allows you to do it all in one. In our example - try this:=SUMPRODUCT(B2:B4,C2:C4)Give Sumproduct a shot— and let us know what you think it’s most useful for!

Technical Skill Tuesdays: Meet f(x)=Sumproduct !

As we mentioned last week, sharing is caring in the Excel universe. Learn a trick to teach to a coworker, and you’re suddenly the go-to for Excel wisdom. If that’s a burden you’re interested in bearing, this segment is for you!

Let’s set the stage for this week’s Excel scenario:

You are planning an event for your University Alumni Association. Non-members have to pay a premium to attend the event, members pay slightly less, and recent grads pay the least. The event coordinator asks you to create scenarios showing how many people in each group you need in order to cover the cost of the event.

You set up a row for each type of attendee and include both the cost of the ticket by attendee type and the number of projected attendees in two separate columns. By multiplying these two columns and summing the three rows’ totals together, you know how much revenue the members will contribute to the cost of the event.

How do you do it all in one fell swoop? I’m glad you asked! As it turns out, there’s a nifty little function in Excel called “Sumproduct.” A very easy way to understand this is to think about the “Sum of Products” - Microsoft probably could have been more intuitive in the way they named it, but we’re talking about engineers in an office environment famed for its cheap pizza, on-site showers, and annual paintball-a-thon. We’ll forgive them for being slightly inaccessible to the rest of humanity.

When is Sumproduct actually worth using?

Since we only set up three different pricing tiers in this model, it isn’t THAT laborious to manually multiply and then add. But, imagine if you had 15 different attendee types. Or worse, 150! It’s at this point of calculation that Sumproduct really hits its stride.

To “sum” today’s TST up (no groans, please):

Instead of multiplying each row, and then adding them together, Sumproduct allows you to do it all in one. In our example - try this:

=SUMPRODUCT(B2:B4,C2:C4)

Give Sumproduct a shot— and let us know what you think it’s most useful for!

  1. levolove posted this
‹ ›