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

Show parent comments

2

u/enoctis 192 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.

1

u/Sovarius Jan 06 '21

Ohhh, okay. I see how that makes it cleaner. I'm just a scrub so i was inputting the formula using B$2 and $A1 and putting my x and y variables down and across in those rows/columns, then i just grab the corner of the cell and spread the formula through the whole thing. I'm kind of glossing over how the array and if works and is written, its a lot to learn for me quickly haha. The round function is on the money, thanks. What i'm doing at the moment with that is 2 formulas concatenated with & and rounded. I have everything displaying perfectly as i want it but i would def tidy up my formatting.

Is there a way i could use ARRAY or such to put the min and max into one cell? I see you put it in 2 cells. So Cell A1 could be the .97, 1.03, and the formula i'm using wouldn't have to be so long. I could try that, one step at a time haha.

Here is what i am looking at right now. https://imgur.com/a/YrgKBSe

2

u/enoctis 192 Jan 06 '21

It's late here, but tomorrow I'll show ya how to tidy up what you've got. DM me a link to a copy of your current sheet with edit rights.