Hey I need to use both RANDBETWEEN and IF function in same cell? I have to generate random data, but if the cell value is 0 then I also need the random value to be 0..


First, do you realize that RANDBETWEEN() function will generate new and new value every time when you open your workbook (and every time when you worksheet will be recalculated, e. g. by pressing Shift+F9 , so you will NOT have something as constant random value forever?

So suppose that the cell A1 contains a value by which the random value is generated - if it is nonzero. Then the formula (in some other cell) will be

=IF(A1=0, 0, RANDBETWEEN(50, 60))
  • yeah! it has to be totally randomised because it's a business sales simulation and we have to use assumptions - this totally worked though thank you so much! – m.j.p Nov 2 '17 at 17:45
  • You're welcome. Please consider to accept my answer (click on the check mark) if it was useful for you. – MarianD Nov 2 '17 at 17:54
  • To supplement @MarianD's comment, by accepting an answer (your question was clearly answered) you will let others know you no longer need help. – LPChip Nov 2 '17 at 18:24
  • Hey! just another one re RANDBETWEEN, say I only want it to be either 0 or 10, nothing in between? – m.j.p Nov 2 '17 at 19:20
  • Use 10 * RANDBETWEEN(0, 1) instead of RANDBETWEEN(50, 60) – MarianD Nov 2 '17 at 19:44

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.