Random Number Selection with Criteria and Median

[ChetManley]

Hello everyone,

I was wondering if it would be possible to select a random number from an existing list based on criteria and having the return "random number" be within a range defined by the median (15% above and below the median).

Customer No.	Amount
123	$50
123	$300
234	$70
456	$22

End result would be having a final table where 5 random Amounts are selected for each Customer - unlike the above example, the data I have customers with hundreds/thousands of Amounts recorded, i.e. one customer can have 000's of these.
Meaning one of the criteria would have to be Customer No.

Customer No.	Random Amount
123	###
234	##
456	###

Use Customer No. as reference (1st Criteria) to select 5 random amounts (for that specific Customer No.), where these 5 random amounts are 15%<= MEDIAN =>15% amount for that specific Customer No.

I think I have the logic in a solid place - just can't seem to translate it into Excel formula.

Anyone able to help?

Much appreciated,
Chet

 

[jasonb75]

Logic and reality are 2 different things. While it might appear that it should be possible, practical would be unlikely.

To do any random selection with a formula requires the use of volatile functions. To apply criteria in the manner you describe would most likely need some very complex arrays.
Combine this with potentially thousands of rows of data and you have a delay of several seconds (possibly even minutes) every time you try to add a single data entry to the sheet, even if that entry is unrelated to the random selection.

In addition, there is only a guaranteed median if there is an odd number of records. For an even number of records, there might not be anything within 15% of the true median.
A prime example of this is customer 123 in your first table. The median of the amounts {50,300} is 175, a variation of +/- 15% gives you a range of 188.75 to 201.25 which doesn't cover any of customer 123's transaction amounts.

 

[ChetManley]

Hi @jasonb75 - I see what you mean..
But there might be a work around I think? What about using COUNTIF for total number of instances of "Amounts" for a particular customer no.?
Would it be possible to the COUNTIF / 2 to get the middle position and return amounts that are within 15% of that position?

 

[jasonb75]

It might be possible, but the results would be biased towards one end of the scale. Using customer 123 again, that would set the range to look at as either 42.5 to 57.5 or 255 to 345 depending on whether you base the middle position on a Low > High or High > Low order.

In addition, considering the points of complexity that I mentioned, attempting something like this would increase the complexity of the formulas even more.
Then there is the possibility of less that 5 results within the +/-15% range. You can't pull 5 random items from a list that only contains 3.

Personally, I would use filters on the original data list rather than trying to extract the detail elsewhere, but this would only work for 1 customer at a time.

Beyond that, I wouldn't even attempt to do this with formulas.

 

[ChetManley]

It might be possible, but the results would be biased towards one end of the scale. Using customer 123 again, that would set the range to look at as either 42.5 to 57.5 or 255 to 345 depending on whether you base the middle position on a Low > High or High > Low order.

In addition, considering the points of complexity that I mentioned, attempting something like this would increase the complexity of the formulas even more.
Then there is the possibility of less that 5 results within the +/-15% range. You can't pull 5 random items from a list that only contains 3.

Personally, I would use filters on the original data list rather than trying to extract the detail elsewhere, but this would only work for 1 customer at a time.

Beyond that, I wouldn't even attempt to do this with formulas.

Hi jasonb75, thanks for the reply.

I need to find a way to extract those 5 random numbers associated to a single customer (I have thousands of customers)..
Starting to look at SQL to see if there's another path I can take.

Cheers
Chet

 

[jasonb75]

For a single customer or for each single customer?

In reality, how may rows of data are we looking at in the entire list? If I take your theroetical examples literally then that would mean that you have several full sheets of data, if that is the case then you might be better off using a database rather than a spreadsheet.

Should I assume that data will be in chronological order rather than customer order?

 

[ChetManley]

For a single customer or for each single customer?

In reality, how may rows of data are we looking at in the entire list? If I take your theroetical examples literally then that would mean that you have several full sheets of data, if that is the case then you might be better off using a database rather than a spreadsheet.

Should I assume that data will be in chronological order rather than customer order?

To each single customer - the data should not fully fill a single sheet (at least from the extracts I tested).
The data is in no particular order - the end goal would be to be able to select 5 random amounts belonging to each customer - where applicable, i.e. if a customer only has 3 total then show the 3 existing.
The ideal scenario would be the above plus a few criteria to add more randomness to the process by adding the 15% median range to it.

I think SQL might be the answer here - I know it can do random selection, I just wonder if the rest of the selection criteria would be possible to implement (but that's a question for other forums).
Excel-wise would this / something similar be implementable?

CHeers
Chet