Math question - Probably 5 grade level (1 Viewer)

[TheIronSheik]

It's pretty well documented that I suck at the math. Completely terrible. Beyond comprehension.

So with that in mind, I've run across a problem I need help with. And remember, when replying, I am an idiot. So word your reply accordingly.

Here's the issue:

I track a lot of data with our company. A couple of the things they are looking for are Averages of certain things. Like, Avg. number of tickets opened for each issue. Or how many tickets are opened for each store. This all sounds really easy, but I've run into a couple of things.

An example would be (simplified):

There are 20 stores. 19 of those stores have 2 tickets in May. 1 of those stores has 1000 tickets in May. The average tickets each store had in May is almost 51 tickets. But obviously, this is not true. One store is affecting the average.

I know there's a way to get the "real" average. I just don't know how. I've heard people talk about "weighted averages", but again, I have no clue how to do this, even after looking it up. I'm just that dumb.

Anyone able to help in terms that I'd be able to understand?

TIA

TIS

 

[the moops]

just use median. middle #

 

[fred_1_15301]

It's pretty well documented that I suck at the math. Completely terrible. Beyond comprehension.

So with that in mind, I've run across a problem I need help with. And remember, when replying, I am an idiot. So word your reply accordingly.

Here's the issue:

I track a lot of data with our company. A couple of the things they are looking for are Averages of certain things. Like, Avg. number of tickets opened for each issue. Or how many tickets are opened for each store. This all sounds really easy, but I've run into a couple of things.

An example would be (simplified):

There are 20 stores. 19 of those stores have 2 tickets in May. 1 of those stores has 1000 tickets in May. The average tickets each store had in May is almost 51 tickets. But obviously, this is not true. One store is affecting the average.

I know there's a way to get the "real" average. I just don't know how. I've heard people talk about "weighted averages", but again, I have no clue how to do this, even after looking it up. I'm just that dumb.

Anyone able to help in terms that I'd be able to understand?

TIA

TIS

51 is the average. Not sure what you mean by "real" average. If you're really concerned about this outlier, just remove it and re-calculate the mean. Either that or just use the median.

 

[Norman Paperman]

Average is 51 hth

You could explain it and say that one store sucks, and the remaining stores average 2.

 

[Cjw_55106]

If one store is that outrageously different, you can throw out the highest and lowest number and get the average of the rest.

 

[fred_1_15301]

By the way, the average was actually 51.9. Wow you really are bad at math........

 

[BroadwayG]

Just roll some bones.

 

[Ilov80s]

Moops is right, you just want the median.

 

[mr roboto]

Median = middle number of a set. Evens out the outliers.

 

[Galileo]

An example would be (simplified):

There are 20 stores. 19 of those stores have 2 tickets in May. 1 of those stores has 1000 tickets in May. The average tickets each store had in May is almost 51 tickets. But obviously, this is not true. One store is affecting the average.

TIA

TIS

But, IT IS TRUE. Your average is 1038/20 = 51.9. If you want to start weighting the value of one store over others, you can certainly do that, but you would no longer have a true average. What criteria would you use to weight one result differently than another? You can make the data say pretty much anything you want if you start doing that.

 

[fred_1_15301]

No offense but I'm a little concerned that you're tracking the data for your company.........

 

[TheIronSheik]

By the way, the average was actually 51.9. Wow you really are bad at math........

That's almost 51.

 

[Norman Paperman]

Well, if you are generating a report I don't think you want to hide the outlier - thats the one you want to emphasize. If you just submit something with the median of 2, someone is going to look at that and think everything is normal. If you submit an average of 51.9, and someone was expecting <5, then you start asking questions and discover one store is exceptionally poor.

 

[Maurile Tremblay]

Average usually means mean (51.9), but it can also mean median (2) or mode (2).

Which of those measures, if any, is appropriate for your purpose depends on your purpose.

 

[fred_1_15301]

By the way, the average was actually 51.9. Wow you really are bad at math........

That's almost 51.

In math, we tend to round up if the first digit after the decimal point is "5" and above.

 

[fred_1_15301]

Well, if you are generating a report I don't think you want to hide the outlier - thats the one you want to emphasize. If you just submit something with the median of 2, someone is going to look at that and think everything is normal. If you submit an average of 51.9, and someone was expecting <5, then you start asking questions and discover one store is exceptionally poor.

This is a very good point. What is the purpose of the report?

 

[Notorious T.R.E.]

This all sounds really easy

It is.

 

[Galileo]

Well, if you are generating a report I don't think you want to hide the outlier - thats the one you want to emphasize. If you just submit something with the median of 2, someone is going to look at that and think everything is normal. If you submit an average of 51.9, and someone was expecting <5, then you start asking questions and discover one store is exceptionally poor.

So, the real question is are you trying to hide something or highlight something??

If neither, then simply present your data "as is" to the interested parties and let them draw conclusions.

 

[JaxBill]

I think you're being too hard on yourself.

I think mean,median, and mode is probably 7th grade.

http://www.youtube.com/watch?v=oNdVynH6hcY

 

[Captain Quinoa]

I asked my 5th grader. He says you're an idiot.

 

[Patriotsfatboy1]

A drone would give you the average number of tickets by store in May. A good drone would give you the median and the average number of tickets by store in May. A valued staff member would tell management why the hell one store had so many damn tickets in May (standard deviations through the roof) and offer up some possible solutions.

 

[Norman Paperman]

:waveshand:

Sheik is not the Drone you are looking for.

 

[Drifter]

min, max and median are likely what you want to report.

 

[TheIronSheik]

Like I said, I was oversimplifying it to explain it.

The median seems to work OK. It's not exactly what I'm wanting, but it'll work for one of my issues.

 

[Notorious T.R.E.]

Pin it.

 

[bostonfred]

Nobody in management wants to talk about means medians and modes. Show it in a regular bar graph. Its easy to see visually and the outlier at the far right will stand out without further explanation. If you want to use a fancy bar graph there's one that puts a line across it with the average, but you don't really need that.

 

[Drifter]

Like I said, I was oversimplifying it to explain it.

The median seems to work OK. It's not exactly what I'm wanting, but it'll work for one of my issues.

You might consider a defect report instead. Define what your threshold is for the "acceptable" number of tickets per store and report on the number off stores above that threshold.

You could also set your formula up as an AverageIf and exclude anything that is greater than 2 standard deviations from the mean. This will remove your extreme outliers but will also hide your out of control variation.

 

[IvanKaramazov]

No -5^2 reference yet?

 

[Patriotsfatboy1]

Nobody in management wants to talk about means medians and modes. Show it in a regular bar graph. Its easy to see visually and the outlier at the far right will stand out without further explanation. If you want to use a fancy bar graph there's one that puts a line across it with the average, but you don't really need that.

If you have a manager that expected a visual depiction of 19 locations with identical numbers and 1 with 500x the others, then he should be shot. I would rather someone spend the time to give me reasons why and mitigations/remedies than making a pretty picture. Then I would be looking to fire the person responsible for those issues because it should have been found long before now.

 

[Ned]

I asked my 5th grader. He says you're an idiot.

 

[mr roboto]

Guys I'm pretty sure the outlier isn't quite as extreme as the example not are the other point quite as uniform.

 

[bostonfred]

Nobody in management wants to talk about means medians and modes. Show it in a regular bar graph. Its easy to see visually and the outlier at the far right will stand out without further explanation. If you want to use a fancy bar graph there's one that puts a line across it with the average, but you don't really need that.

If you have a manager that expected a visual depiction of 19 locations with identical numbers and 1 with 500x the others, then he should be shot. I would rather someone spend the time to give me reasons why and mitigations/remedies than making a pretty picture. Then I would be looking to fire the person responsible for those issues because it should have been found long before now.

and the graph should have those dots on it with the numbers.

 

[TheIronSheik]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

It's nothing major, and I have nothing to do with the actual tickets. I'm merely tracking data and compiling it into reports. But when I'm in the meetings and numbers seem to be off because a couple of stores are inflating the average, I tend to think there could be a better way.

Also, this place has kind of gone down hill a bit. I started off by saying I suck at math. And then people post in here "You suck at math!" This is what qualifies as good FFA posts nowadays? I know how old people feel when they look at young kids now.

 

[bostonfred]

You suck at math!

 

[McGarnicle]

Average = 2. Sell the branch with the 1000 tickets.

 

[Captain Quinoa]

Then shouldn't you/they be looking at a rate (like # of tickets vs sales by store) rather than a straight number of tickets?

 

[Patriotsfatboy1]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

It's nothing major, and I have nothing to do with the actual tickets. I'm merely tracking data and compiling it into reports. But when I'm in the meetings and numbers seem to be off because a couple of stores are inflating the average, I tend to think there could be a better way.

Also, this place has kind of gone down hill a bit. I started off by saying I suck at math. And then people post in here "You suck at math!" This is what qualifies as good FFA posts nowadays? I know how old people feel when they look at young kids now.

So, wouldn't you then take a look at a different stat than raw tickets? If a store is considered large because of number of customers or dollar volume, then do a number based on the number of tickets per ($ or customer) to adjust for different sized locations.

The whole point with stats like this is that they have to tell you something about where you can make an improvement. Just because you have 5% of your stores that are outliers, it does not necessarily mean that you can't make a positive impact by fixing a small percentage of your stores.

You aren't really explaining the issue very well and it may be that you are more than just sucky at math.

 

[Maurile Tremblay]

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

I don't really know what a ticket is. But for each store, instead of looking at total tickets for that store, it might be helpful to look at tickets divided by sales volume or something.

 

[Drifter]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

It's nothing major, and I have nothing to do with the actual tickets. I'm merely tracking data and compiling it into reports. But when I'm in the meetings and numbers seem to be off because a couple of stores are inflating the average, I tend to think there could be a better way.

Also, this place has kind of gone down hill a bit. I started off by saying I suck at math. And then people post in here "You suck at math!" This is what qualifies as good FFA posts nowadays? I know how old people feel when they look at young kids now.

What about tickets per employee or tickets per weekly revenue? Or set up tiers and separate them that way?

 

[bostonfred]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

Maybe you need a ratio like tickets/sales volume or something. It sounds like you expect greater sales volume to lead to more tickets. Is that pretty linear? Like a store that does 1 million in sales should have 100 tickets and 5 million should have 500?

 

[Long Ball Larry]

you suck at math!

Also, I agree with the last few posts that you should be tiering them in some way and/or trying to get some common denominator.

Maybe monthly % change in tickets (as compared to prior month)?

 

[msommer]

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

I don't really know what a ticket is. But for each store, instead of looking at total tickets for that store, it might be helpful to look at tickets divided by sales volume or something.

Or show both. That will show that where the problem is immediately

ETA: I think tickets refer to support requests. That's what been told a number of times by the it-support call center when I have exhausted the poor guy's IT knowledge (ie. turning it off and then on again didn't work) - "I'll have to open a ticket"

Usually there is a hint of an accent going on too

 

[Corporation]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

It's nothing major, and I have nothing to do with the actual tickets. I'm merely tracking data and compiling it into reports. But when I'm in the meetings and numbers seem to be off because a couple of stores are inflating the average, I tend to think there could be a better way.

Also, this place has kind of gone down hill a bit. I started off by saying I suck at math. And then people post in here "You suck at math!" This is what qualifies as good FFA posts nowadays? I know how old people feel when they look at young kids now.

What about tickets per employee or tickets per weekly revenue? Or set up tiers and separate them that way?

I was going to suggest tiers as well.

 

[Dinsy Ejotuz]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

It's nothing major, and I have nothing to do with the actual tickets. I'm merely tracking data and compiling it into reports. But when I'm in the meetings and numbers seem to be off because a couple of stores are inflating the average, I tend to think there could be a better way.

Also, this place has kind of gone down hill a bit. I started off by saying I suck at math. And then people post in here "You suck at math!" This is what qualifies as good FFA posts nowadays? I know how old people feel when they look at young kids now.

Do something like tickets/sale or tickets/$1,000 of revenue or the like -- that takes the size of the store into account.

 

[Ned]

The last few posts are all hitting on the same thing; weighted average. Figure out a way to quantify the size of each store.

 

[TheIronSheik]

Then shouldn't you/they be looking at a rate (like # of tickets vs sales by store) rather than a straight number of tickets?

We should. But they don't want to spend time rating the stores. They feel this would be a waste of time. I, on the other hand, disagree with them and have been pushing for this since I got here.

 

[TheIronSheik]

I'm not trying to hide anything. It's actually a quite detailed report I present each month for upper management. The problem that occurs is that UM tends to focus on numbers that seem out of place.

We have over 450 stores nationwide. Some of these stores are huge while others are very small. If a small store has a lot of tickets, that's an issue. But if a large store has the same amount of tickets, it's not a problem.

Of the 450 some stores, there are usually about 20 to 30 stores that somehow mess up the stats. If you remove those 20 stores, our average falls right into the threshold that's allowed. But usually, one of the bigger stores (like a Manhattan or Los Angeles store that is extremely high volume) will produce large amounts of tickets. These throw off the average of the other 420 stores.

It's nothing major, and I have nothing to do with the actual tickets. I'm merely tracking data and compiling it into reports. But when I'm in the meetings and numbers seem to be off because a couple of stores are inflating the average, I tend to think there could be a better way.

Also, this place has kind of gone down hill a bit. I started off by saying I suck at math. And then people post in here "You suck at math!" This is what qualifies as good FFA posts nowadays? I know how old people feel when they look at young kids now.

So, wouldn't you then take a look at a different stat than raw tickets? If a store is considered large because of number of customers or dollar volume, then do a number based on the number of tickets per ($ or customer) to adjust for different sized locations.

The whole point with stats like this is that they have to tell you something about where you can make an improvement. Just because you have 5% of your stores that are outliers, it does not necessarily mean that you can't make a positive impact by fixing a small percentage of your stores.

You aren't really explaining the issue very well and it may be that you are more than just sucky at math.

This is my bad. I started writing the post and then got very busy. So I tried to condense it quickly.

 

[Drifter]

Then shouldn't you/they be looking at a rate (like # of tickets vs sales by store) rather than a straight number of tickets?

We should. But they don't want to spend time rating the stores. They feel this would be a waste of time. I, on the other hand, disagree with them and have been pushing for this since I got here.

Doesn't take any time at all to put them in tiers. Look at their annual revenue and split them into 3 groups. A, B, C. Lots of retail does this.

 

[TheIronSheik]

Then shouldn't you/they be looking at a rate (like # of tickets vs sales by store) rather than a straight number of tickets?

We should. But they don't want to spend time rating the stores. They feel this would be a waste of time. I, on the other hand, disagree with them and have been pushing for this since I got here.

Doesn't take any time at all to put them in tiers. Look at their annual revenue and split them into 3 groups. A, B, C. Lots of retail does this.

I understand that. I don't have access to the annual revenues of these stores. And the people that do don't have the time to work with me to determine tiers.

 

[Long Ball Larry]

Then shouldn't you/they be looking at a rate (like # of tickets vs sales by store) rather than a straight number of tickets?

We should. But they don't want to spend time rating the stores. They feel this would be a waste of time. I, on the other hand, disagree with them and have been pushing for this since I got here.

Doesn't take any time at all to put them in tiers. Look at their annual revenue and split them into 3 groups. A, B, C. Lots of retail does this.

I understand that. I don't have access to the annual revenues of these stores. And the people that do don't have the time to work with me to determine tiers.

Can you determine the average number of tickets for the last 12 months for each store and then tier them that way?