annoying math problem

[Glenn E.]

It is annoying only because I believe that I should know the answer, but my college Math major is rusty enough that I do not.

I need to do a weighted average in 2 dimensions.

cost averaging.jpg (224.99 KiB) Viewed 405 times

Horizontal axis lists quantity ordered.
Vertical axis lists page count.
Cells contain the price of a book at that size (page count) and in that quantity (quantity ordered).
The meaning of the two yellow-shaded cells isn't relevant for this discussion.

As you can see, the grid is sparsely populated and I suspect that's what's causing me problems.

The light blue row computes the weighted average of the columns.
The light green column computes the weighted average of the rows.

The dark blue cell is then an attempt to compute the weighted average of the light blue line across the quantities ordered.
The dark green cell is then an attempt to compute the weighted average of the light green column across the page counts.

In my head, those two numbers should be the same. I thought they would serve to cross check each other. Obviously, they do not.

Why not?

Also, since my plan did not work in order to generate a weighted average across both page counts and quantities ordered, how do I do that?

[jdaw1]

This question just seen. Sigh.

Why weighting the average of the columns? Seems weird to do that by page count.

You need three summary rows:
• number books = TopRow * CountIfs(…, ">0")
• total cost = SumProduct(…, …)
• Ratio of these two.

If you must weight by pages — takes all sorts — then do equivalent in columns. And total of counts should agree; total of costs should agree.

[Glenn E.]

What we pay for books varies by many things, but the main drivers are the number of pages (because more paper costs more money) and the quantity that we order (a larger order gives a volume discount).

I was asked to figure out the average price we've paid for all books over the last few years. I could just average the prices without paying attention to page count or order quantity, but that seems inaccurate to me. Those few orders for 50,000, 35,000, or 28,000 books should weigh more heavily on the average price paid for a book. Likewise, the 464-page books should weigh more heavily than the 128-page books.

I have (I believe) correctly done weighted averages for the columns, as show in the light blue cells. For any given quantity ordered, I can tell you the weighted average paid for those books with the weighting done by the physical size of the book.

I believe that I have also correctly done weighted averages for the rows, as show in the light green cells. For any given page count, I can tell you the weighted average paid with the weighting done by the order quantity.

Is the problem that I shouldn't be doing this as a 2-dimensional grid, but instead simply a list of [pages, quantity, cost] where the weighted average is then just sum(pages*quantity*cost) / sum(pages*quantity)?

[jdaw1]

So you are seeking an avearge cost per page? Sure, have a column of count, count*pages, count*pages*price, and do what needs to be done.

But the model is flawed: you are assuming covers are free. Surely you should fit a linear, costing being covers + numPages * perPage.

[Glenn E.]

Yes, the cover itself would be more-or-less a fixed cost, but it should be captured by doing a weighted average. It disproportionately adds to the cost of a smaller book, yes, but that's reflected by the increased price that is calculated into the weighted average. We're pricing a book, not pages, even though page count is one of the measures that is being weighted.

I have since been told by the requestor that I'm being far too precise with my answer. Funny. Then why ask me to answer the question? Surely they have met me before, haven't they? They should know better than to ask me a question that can be answered accurately, but then expect to get a vague approximation.

I have now been diverted to answering a question for US Customs and Border Protection. Perhaps I should stop answering questions so accurately so that I might no longer be asked questions that need accurate answers.

I will eventually come back to this, because now that the question has been asked I need to know the accurate answer even if the requestor does not.

[Alex Bridgeman]

I think Julian is right (no surprise really). The average cost of a book is equal to the average cost of a cover plus the average cost per page x average number of pages. You should have enough data to solve the simple two unknown formula.

You can make your life easy by assuming either of:
(i) No inflation, or
(ii) linear inflation over the period in question

But if you really want to give an accurate answer you can get figures for paper and/or book inflation over the last few years and correct the costs from money of the day to real cost and then back to today’s money.

Go on, you know you want to give the accurate answer..

[Glenn E.]

I have reduced the number of emails that I need to send to US Customs and Border Protection to a mere 284, all of which now need to be examined individually to determine suitability. Ugh. Too much for one day. Back to the weighted average cost of books.

My initial attempt above gave 2 different answers - 4.46 and 4.58. The requestor decided that was close enough, and averaged those 2 numbers (getting 4.52) not caring why they were different.

I have since re-performed the calculation using a simple list of [pages, quantity, cost] weighting by pages*quantity. This gave a result of 4.68. Interesting that it is higher than either of the initial numbers. I assume due to the fact that 50,000 books at 464 pages, when multiplied, adds a tremendous amount of weight to that price of 5.51. Easy to test... let's put in a second order of 50,000 books at 464 pages... oh look, now the weighted average is 4.79.

Clearly, the initial calculation of weighted averages across rows or columns, and then re-weighting the resulting averages across the opposite columns or rows, did not sufficiently weight the extremes. But why not?

[PopulusTremula]

A naive question perhaps but is all paper created equal? Perhaps these books only use one paper type/quality, or the difference in price based on different paper qualities is negligible? If not, then why not also consider the type of paper used?

[Glenn E.]

A very good question because the answer is that no, not all paper is created equal, but in this particular case the books are all ordered with the same paper and in the same physical dimensions.

So as much as is possible, it is the "same" paper each time.

I can spend a great deal of time ranting about extremely minor variances in paper from one mill run to the next, which should be imperceptible to 99.9% of people, but it seems that our customers include the 0.1% and so they complain from one book to the next about changes that are so subtle that you'd need a micrometer (which I have because of these people) to be able to tell the difference.

Need I mention that the same paper from the same mill run can feel different from one book to the next because the books crossed the Pacific on 2 different ships, one of which encountered a squall while the other didn't, and so their average humidities during transit were different?