IFS
You could use nested IF functions but they are hard to follow visually and your use case lends itself well to using the IFS function. IFS is designed to evaluate multiple conditions and return the value associated with the first true condition.
I posted the formulas below in a demo Google Sheet here
# Basic Format
=IFS(condition1, value1, [condition2, …], [value2, …])
# where 'x' is a cell reference
=IFS( x<20, "Low Risk",
x<50, "Medium Risk",
x<100, "High Risk",
x>99, "Evacuate Immediately" )
Impact of Invalid Source Data
The mathematical comparison operators < > behave unexpectedly when numbers are compared to either text or NULL values.
# where cell at ref 'x' has value "this"
# where cell at ref 'y' has NULL value
=x<2 =y<2
="this"<2 =""<2
=FALSE =TRUE
=x>2 =y>2
="this">2 ="">2
=TRUE =FALSE
This can be avoided using only numbers in the data but optionally the formula can test for these values and manage.
# where cell at ref 'x' has value "this"
# where cell at ref 'y' has NULL value
# Test for text value
=ISTEXT(x) =ISTEXT(y)
=ISTEXT("this") =ISTEXT("")
=TRUE =FALSE
# Test for NULL value
=x="" =y=""
="this"="" =""=""
=FALSE =TRUE
=IFS( OR(ISTEXT(x), x=""), "",
x<20, "Low Risk",
x<50, "Medium Risk",
x<100, "High Risk",
x>99, "Evacuate Immediately" )
Negative Values
Also note that the formula will return "Low Risk" for negative values, which presumably are an indication of an error. If different behavior is preferred a x<0 check can be added at the beginning of the function.
=IFS( OR(ISTEXT(x), x="", x<0), "",
x<20, "Low Risk",
x<50, "Medium Risk",
x<100, "High Risk",
x>99, "Evacuate Immediately" )
Demo Sheet
https://docs.google.com/spreadsheets/d/1q2Xy_2FZxIC6ToKPUHJEBhV-llg9KMSBgWNa0QB0m_A#gid=2100307022
Hi there! I have a workbook that I use to track all my reading related activities, one of them being a TBR (to be read) list which includes titles, authors and genres of books I want to read. It also has a column indicating whether or not I already own the book. All this data is pulled from a master sheet in the workbook.
In the same sheet, I have a “random book generator”. I select the genre I want to read then click the check box to randomly generate a book that is of said genre. (It will also tell me if I own the book or not). What I am trying to do is display text like “you do not have any books on your TBR that identify as the selected genre” if that criteria is met. Right now it just displays the default #N/A. I think I copied the random generator formula from the internet somewhere so I’m not too comfortable messing with it. I’ve tried changing the IF to IFS and adding M7=“#N/A”, “you do not have any books on your TBR that identify as the selected genre” At the end but that didn’t work. It was a half hearted attempt lol.
Any help would be appreciated!
Picture 1: the table of books Picture 2: the random book generator Picture 3: the formula in cell M7 which would either display the title of the book, or in this case #N/A since I don’t have any nonfiction books on my list. This is the cell that I want to display “you do not have any books on your TBR that identify as the selected genre” instead of #N/A.
Got a document where i need to figure out a way to make cells in one column display the text “done” or “completed” based on percentage value in a adjacent column, only when that percentage value is at 100%. When the value is below 100, it should display “in progress” instead.
If there is other ways that using formulas to achieve this, im open to those options.
Hello!
I am creating myself a grocery list that will display the name and location of an item on a list on another google sheet (same workbook) based on if the quantity of a cell <0 (if I need it or not) but I don't know how to display another cells data based on the first cell. If someone could help me I would gladly share a Blank copy for anyone to use once its completed because I know its going to be super useful, Thanks!
Try this:
=M1&O1&N1
There's no need for the quotation marks in your existing formula, and I think this is where the error is creeping in.
TROUBLESHOOTING
=len(M1)- the result should be 3=len(N1)- the result should be 15=len(O1)- the result should be 1
The total length of =M1&O1&N1 should be 19.
See my comment to your original post. For now, I'll assume that you do have a header in F1 and that your actual addresses begin in Row 2.
Select Col F and delete everything (including the header).
In F1, place the following formula:
=ArrayFormula({"Full Street Address"; IF(N2:N="",,IF(M2:M="",,TRIM(M2:M)&"-")&TRIM(N2:N))})
You don't need Col O at all.
This one formula should produce the header (which you can within the formula itself as you like) and all results for all rows.
I believe you can achieve your desired result without any formulas at all by using 'meta instructions' in a custom number format. Apply the following custom number format to your number cells:
[<1000]###.##;[>1000]#,###
You can then use conditional formatting to colour cells >2000 in the standard way because you no longer need to coerce numerical values to text using a formula in order to achieve the desired format.
Here's one approach you may test out:
=let(Σ,Sheet1!C5,
if(Σ>999,query(Σ,"format Col1 '#,###'"),query(Σ,"format Col1 '###.##'")))
PS: @Harun24hr suggestion(in the comments) should also work provided you use the Custom formula setting within conditional formatting