So i have a sheet that i want to generate a given number of random numbers between a range every time i go in to the sheet. I am able to create the min and the max for thesis number but i cannot figure out how to make it list the qty of numbers. I have attached a sample sheet.
Also trying to figure out a formula to list all of the outliers for the set of data created.
Thanks in advance for your help.
Los
In A1 and filled down
=IF(ROW()>$D$1,"",RANDBETWEEN($D$2,$D$3))
Then you'll need to adjust the range in each of your formulas in D5:D9
Change A1:A56 to A1:INDEX(A:A,$D$1)
You were so close:
=IF(ROWS(A$1:A1)<=$D$1,RANDBETWEEN($D$2,$D$3),"")
in A1, copied down. Changing the ranges of the statistcal rows is not required, but they should all go to the same row...
Last edited by Glenn Kennedy; 06-06-2017 at 10:02 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Losguapos,
See the attached for a CountIf function and I think what you want.
Countif Random Data sample.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I'm not sure what you mean by "... list the qty of numbers ..."
Do you mean the total (SUM) of the numbers, or the count (COUNT) of how many numbers you have generated? How does "...list..." come into it?
What determines the lower value and higher value below/above which the outliers will be compared?
Pete
Thank you for the help,
Glenn Kennedy your solution worked like a charm, now i just need a way to list all of the outlier if there are any. Any suggestions on that?
Try this, but with the nature of random numbers, you're unlikely to get any... Now you DO need to adjust the ranges, daynamically as suggested previously by JonMo.
these are array formulae. For lower-bound outliers:
=IFERROR(INDEX($A:$A,SMALL(IF($A$1:INDEX($A:$A,$D$1)<$D$11,ROW($A$1:INDEX($A:$A,$D$1))),COLUMNS($D13:D13))),"")
in D13, copied across. A similar formula was used in E14.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks