Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working in Excel and want to calculate the relative price level in 4 markets.

I know a good method I can use when I am only looking at 2 markets

             USA    UK
Bubble gum    4     6
Lollypop      3 
Chocolate bar 7     8

In the above example with 2 markets I would conclude that the price level is (6/4+7/8)/2 = 1,1875 higher in the UK than it is in the USA.

We can express this as USA = 1 UK = 1,1875

But how would I calculate this when we have four markets and don't have prices for all items in all markets?

             USA    UK    France    Germany
Bubble gum    4     6       8
Lollypop      3             5          4
Chocolate bar 7     8      10

I need your help calculating the price levels in the example above. I'd like to do the calculations in Excel if possible.

share|improve this question
you need weights to get comparable price-levels results, usually volume of goods consumed - implicit equal weights for all goods are evidently wrong, since some of the goods have no price => they might have 0 quantity – Aprillion Apr 17 '13 at 10:05

You could use data for the purchasing power parity (PPP).

Obtain values for the PPP of the countries involved calculated by an institute such as the world bank or the IMF. Then calculate the "international price" $\pi_i$ of good $i$ of $I$ by country $j$ of $J$ by this formula

$$\pi_i = \sum_{j=1}^J \frac{p_{ij}} {PPP_j}\frac {q_{ij} }{Q_i} $$

Where the $p_{ij}$ is the price of good $i$ in country $j$ and $PPP_j$ is the purchasing power parity calculated from a more substantial bag of goods.
Of course you'd also need the consumed amounts per good for each country $q_{ij}$ and their total $Q_i$ ie. $\sum_{j=1}^J q_{ij}$ or at the very least the percentage of consumption for these goods which you might input instead of the fraction.

In theory this would give you an international price of each good based on the current PPP niveau. This then you could compare to the actual prices in your samples.

Of course all PPP assumptions apply (free trade, no transaction costs etc. etc...), so note that in economic terms this is very theoretic, in that it empirically holds long term only if at all.

Just an idea to keep it simple.

share|improve this answer

You have to assume one country as the base, and then you need to compute price of country i relative to that base country.

share|improve this answer

If you have a country with complete data, you might use it as the "base" country, as suggested in another answer. If all of them are incomplete, things are a bit more complex.

I would impute all missing prices using the EM algortihm (see, for instance, R package norm) and then use the completed data as per the suggestion above.

I am assuming that you have data for a single point in time; if you have data for different times, then perhaps the methodology in CASTROVIEJO, P.M. y TUSELL, F. (2007) Using redundant and incomplete time series for the estimation of cost of living indices, Review of Income and Wealth, vol. 53, p. 673-691. can be of help.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.