# Discussion on: Use Excel to obtain a random sample of your customers for market analysis

5

### Join the conversation!

View:
Show:
###### RAND function caveat
cisauditor 28th Nov 2007
Every time you perform an action in Excel, the automatic recalculation feature is going to re-perform all the RAND functions. So, when you perform the Sort function, all the RAND functions are going to change. To remedy this, copy the RAND function column and then use Paste Special, Values to save the static values calculated rather a dynamic formula.
###### Sampling and Customer Representation
bob.ellis@... 28th Nov 2007
Random sampling is important and Mr Lawrence's reinforcement of the "paste special" function has merit. However, also keep in mind that random sampling may bias your analysis if the sample does not represent the characteristics of the analysis you are attempting on your customer base. For example, if you have a customer base that is structured in a Pareto effect (i.e., customer types have a 70%, 20%, and 10% breakout) then you need to review your sample to ensure that the percentages of the sample are similar to the customer base percentages. Sampling at best is only as good as the customer representation.
Contributr
###### What he said!
ssharkins@... 10th Dec 2007
I generally take on only simple development projects. I'm just not interested in long-term, serious hard core development solutions. They make my head hurt. Anyway, a few years back, I took on an Access project for a distribution company -- they wanted a "simple" routine for retrieving records they could then analyze to measure future growth prospects. OH MY!

You wouldn't believe the "conditions" that went into the retrieving a random set. It nearly killed me.
###### Observations
clifforde 3rd Dec 2007
The other 2 posts are very valid.
When I Inserted the =RAND() function, I received .xxxxx. I multiplied by 1,000 to get better numbers. For those folks with, let's say, 3,000 clients, multiply by 10,000 and make sure you have at least 8 digits showing to make the Sort unique. Too few digits and you will get Duplicates.
###### RAND function can generate duplicates
VickyToo 7th Dec 2007
Because the RAND function will randomly assign numbers to each cell, there is potential there for duplicates.

I found a website that gives an example of a customized function that will generate unique random numbers:

http://www.ozgrid.com/VBA/RandomNumbers.htm

Vicky
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.