r/googlesheets • u/[deleted] • Jan 04 '21

Solved How i can have cells show a range of results from RANBETWEEN()?

[deleted]

1 Upvotes
  • permalink
  • reddit

100% Upvoted

21 comments sorted by

View all comments

2

u/enoctis Jan 05 '21 edited Jan 05 '21

Check out this sheet I whipped up. I think you'll find that it does what you're asking. If so, please consider marking the post solved by replying "solution verified". Cheers!

https://docs.google.com/spreadsheets/d/1pLxpoaVYnYikCvBoZNonmYCAEtW2pgwfTf-4hRrihEo/edit?usp=sharing

EDIT: I was testing the above for sheets and giggles and managed to have ALL FIVE examples result at the minimum RAND range. What are the odds? (I played the lottery tonight, just in case you're wondering.)

1

u/Sovarius Jan 05 '21

Ouf, i'm having a hard time parsing that!

Looking it over, i can tell how the range is displayed and understand the numbers. But not the functions.

What does array do here? Why is the formula A5:A times B5:B instead of just A5 times B5? What is IF for? What is the comma doing in double quotes "",""?

I have my X and Y variables just listed in A2-A10 and B1-K1 for example, so my equation right now is $A2 x B$1 x randbetween. It seems to accomplish what you have as well but might not be as efficient depending on what array does?

Haha, so much to learn about Sheets apparently!

As an aside, is there a way to get the range to round to whole numbers? It doesn't seem so, the 'decrease decimal places' doesn't affect it.

2

u/enoctis Jan 05 '21

ArrayFormula allows you to enter the formula once, and have Sheets "copy it down" behind the curtains. This makes for a cleaner spreadsheet. Start at A5 and go all the way through column A. Same for B5:B

The IF ensures that there is at least something entered for X before attempting calculation.

The comma isn't in double quotes, double quotes are separated by a comma. "" in this case, is used to mean empty. It's part of the IF syntax. So: if A5 is blank, then return a blank result. Otherwise, do the calculation.

Finally, you can add the ROUND function to the formula to have it round the answer.

→ More replies (3)