I’ve never seen an easy resource on this before, so here you go: How to graph Van Westendorp data to come up with a graph like the one below.
If you’re doing this sort of thing all the time, you probably have a macro or some template for running these quickly. But I get lots of questions from people doing this for school, or for one report—this walk-through is designed to meet those kinds of needs.
Fist, I’m assuming you’ve asked all the right questions in your survey. But just in case, here are the four questions asked in a Van Westendorp question set:
- At what price would you think product is a bargain – a great buy for the money
- At what price would you begin to think product is getting expensive, but you still might consider it?
- At what price would you begin to think product is so inexpensive that you would question the quality and not consider it?
- At what price would you begin to think product is too expensive to consider?
When your survey is done, download your raw data into Excel or CSV. Copy-and-paste the four columns containing the answers to the four Van Westendorp questions onto a separate sheet. Start the paste in column B. Make sure the question labels are in the first row and in the same order as above (“a bargain” on the left, “so inexpensive” on the right). See image below:
Separate each of the four columns by inserting one column in-between them, like in the image below.
Select cell B1, then click Data>>Filter. Then sort smallest-to-largest. Then select B1 again and de-select filter. Select cell F1 and do the same thing. Columns B and F should then be sorted smallest-to-largest.
Now do the same things for columns D and H. Select cell D1, then click Data>>Filter. Then sort smallest-to-largest. Then select D1 again and de-select filter. Select cell H1 and do the same thing. Columns D and H should then be sorted largest-to-smallest.
(The reason for doing this one column at a time, with a blank column in between, is to make sure we’re only changing one column at a time.)
Remove all the blank in-between columns, so that only columns B, C, D and E have data.
Begin numbering each row in column A, starting at A2. Fill the entire column until every row with data has a number. Each row should contain data in all of columns A, B, C, D and E.
Things should now look like this:
Now add a blank column between A and B. In the new blank cell B2, paste this formula:
Then drag this down to the bottom of your sheet — to the last row containing any data. Then highlight this column (B) and convert to Percent Style. Your sheet should now look like this:
Highlight columns B through F, then click Insert and create a Line graph. The graph should generate and look like this (I renamed mine):
Now, the goal with a Van Westendorp is to highlight the overlap in the middle. So shrink the data selection of your graph in order to make this gap easy to see. You can do that by dragging the top and bottom of your graph’s data selection so that the gap becomes larger. That’s what I did in the image below:
I’m not going to go into any more detail about how to interpret this graph. If you’re here learning how to graph is, I’m assuming you know why you’re doing this in the first place.