"Do computers save time?"
Update below.
This was a flippant question posed by Pepe back in the day. At the time I thought he was exaggerating, but now I'm not so sure...
Anyway, check this out. I have a table in Excel that I reproduce below. Someone vetting my work pointed out that for the average 2006 data, if you take the averages of the percentage column, you get 1.4%, whereas if you directly take the percentage growth of the two average figures, you get 1.0%.
This is not a truncating problem; even if I do this right in Excel, you get a different answer depending which way you do it. So you might say, "Yes Bob, if I do it with my calculator with the numbers you've listed below, you get different answers, but it's because of rounding." But no, I don't think that's it, unless Excel does calculations based on the "shown" value rather than the embedded value with 5 decimal places.
So what the frick? Now do I have to check to make sure Excel does it percentage and averaging operations correctly? I've run into crazy problems before where the Vista version generates graphs that would appall Mandelbrot, if you try to change the domain of the x-axis. So I learned not to mess with that; you just highlight and construct the graph on the data you want, end of story.
But now I have to double check to make sure Excel is correctly doing percentages on a column of 8 numbers? Are you kidding me? Someone please save my sanity.
(BTW, the columns line up nicely in my screen as I pasted them in and lined them up, but when I hit "preview" they are squished together. I'm not going to bother fixing it. I hope you get the idea.)
Oil Consumption
(thous bbls/day) 2006
Region/Country 2005 P2006 Growth
========================================
United States 20802.2 20687.4 -0.6%
China 6720.0 7201.3 +7.2%
Japan 5305.1 5159.4 -2.7%
Russia 2757.0 2810.8 +1.9%
Germany 2647.1 2664.9 +0.7%
India 2438.0 2571.9 +5.5%
Canada 2296.9 2264.0 -1.4%
Brazil 2166.0 2216.8 +2.3%
Korea, South 2191.3 2173.8 -0.8%
Saudi Arabia 2000.0 2139.4 +7.0%
Mexico 2045.2 1996.7 -2.4%
France 1988.3 1961.2 -1.4%
United Kingdom 1834.3 1824.9 -0.5%
Italy 1754.8 1732.3 -1.3%
Iran 1572.0 1685.8 +7.2%
AVERAGE 3901.2 3939.4 +1.4%
Update: Turns out Silas is right--I am an idiot. Obviously the weights matter, and so there is no reason that the average of the growth rates will be the same as the growth rate in the average. Duh. Excel is still prone to weird mistakes that will embarrass you, but this was not one of them.
This was a flippant question posed by Pepe back in the day. At the time I thought he was exaggerating, but now I'm not so sure...
Anyway, check this out. I have a table in Excel that I reproduce below. Someone vetting my work pointed out that for the average 2006 data, if you take the averages of the percentage column, you get 1.4%, whereas if you directly take the percentage growth of the two average figures, you get 1.0%.
This is not a truncating problem; even if I do this right in Excel, you get a different answer depending which way you do it. So you might say, "Yes Bob, if I do it with my calculator with the numbers you've listed below, you get different answers, but it's because of rounding." But no, I don't think that's it, unless Excel does calculations based on the "shown" value rather than the embedded value with 5 decimal places.
So what the frick? Now do I have to check to make sure Excel does it percentage and averaging operations correctly? I've run into crazy problems before where the Vista version generates graphs that would appall Mandelbrot, if you try to change the domain of the x-axis. So I learned not to mess with that; you just highlight and construct the graph on the data you want, end of story.
But now I have to double check to make sure Excel is correctly doing percentages on a column of 8 numbers? Are you kidding me? Someone please save my sanity.
(BTW, the columns line up nicely in my screen as I pasted them in and lined them up, but when I hit "preview" they are squished together. I'm not going to bother fixing it. I hope you get the idea.)
Oil Consumption
(thous bbls/day) 2006
Region/Country 2005 P2006 Growth
========================================
United States 20802.2 20687.4 -0.6%
China 6720.0 7201.3 +7.2%
Japan 5305.1 5159.4 -2.7%
Russia 2757.0 2810.8 +1.9%
Germany 2647.1 2664.9 +0.7%
India 2438.0 2571.9 +5.5%
Canada 2296.9 2264.0 -1.4%
Brazil 2166.0 2216.8 +2.3%
Korea, South 2191.3 2173.8 -0.8%
Saudi Arabia 2000.0 2139.4 +7.0%
Mexico 2045.2 1996.7 -2.4%
France 1988.3 1961.2 -1.4%
United Kingdom 1834.3 1824.9 -0.5%
Italy 1754.8 1732.3 -1.3%
Iran 1572.0 1685.8 +7.2%
AVERAGE 3901.2 3939.4 +1.4%
Update: Turns out Silas is right--I am an idiot. Obviously the weights matter, and so there is no reason that the average of the growth rates will be the same as the growth rate in the average. Duh. Excel is still prone to weird mistakes that will embarrass you, but this was not one of them.
Bob,
ReplyDeleteThis is mathematically correct. Excel is doing nothing wrong.
Extreme example with only two values: The price of a house increases by 10% from $200,000 to $220,000. The price of a soda increases by 100% from $.50 to $1.00. Averaging the %increases gives (10%+100%)/2 = 55%. Adding the prices and calculating the %increases gives ( ($220,001.00-$200,000.50)/$200,000.50 )x100% = 10.000225%.
Yep I'm an idiot. For what it's worth, I realized what was happening and rushed to post an update here before anyone else caught it.
ReplyDeleteSilas?
ReplyDeleteWhere the hell is Silas?
Silas is in Bob's head...
ReplyDeleteWelcome to our game world, Hellgate London Palladium , Hellgate London gold , Hellgate gold , buy Hellgate Palladium and cheap hellgate gold , they are very interesting.
ReplyDelete