Hello,
I used the =RANDBETWEEN function to generate 100 randomly generated numbers down a column. I am having issues when trying to use the =COUNTIF function to search for duplicates. Every time I insert the =COUNTIF function, new numbers are generated.
Any ideas point me in the right direction would be greatly appreciated.
thanks
Everytime your workbook recalculates the values returned by the =RANDBETWEEN() formula change. What are you using the values for?
Sincerely
S?ren Larsen
"Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"
Is there a way to run both functions simultaneously? so when RANDBETWEEN generates the numbers, COUNTIF can check for duplicates?
You can use conditional formatting on the column to highlight the duplicates Or use vba to do the Randbetween and countif and print the results to the sheet
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Not as such. But what are you trying to achieve? There might be an alternative such as:
1) Increasing the limits in the =RANDBETWEEN() can eliminate duplicates
2) Using =RAND() with =RANK() can eliminate duplicates
Thank you for the responses.
I am not trying to eliminate the duplicates. Instead, I am trying to measure how many duplicates there are. Instead of doing it manually, I thought COUNTIF would be the most appropriate. Would I be incorrect in my thinking? thanks
Try disabling the automatic calculation. Go to the 'File' icon (the one you click on to open, save, print, etc. It looks like the Windows logo), and at the bottom of that window choose 'Excel Options'.
Choose 'Formulas' and then change the 'Workbook Calculation' to Manual (and you will likely want to uncheck the 'Recalculate before saving' option.
This will prevent any calculations (including RANDBETWEEN) from occurring. To force a new calculation, you need to hit the F9 key.
Pauley
thank you, Pauley and Abousetta.
I am on Excell 2011 for the Mac. When I tried 'Conditional formatting', it would not let me input a formula. Instead, I had a drop down option of highlighting 'duplicates'. I chose that.
I'm still trying to see if I can directly use the COUNTIF formula.
thanks again
I'm not exactly sure what you are trying to accomplish but here goes:
Please Login or Register to view this content.
i dont get it either, just put =--countif($a$1:$a$100,row(a1))>1 drag down 100 rows then all those marked 1 wild be duplicates
use sum on that to find out how many different numbers are duplicated
or =countif($a$1:$a$100,row(a1)) and you could see how many times each is duplicated
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks