- Google Spreadsheets Formula Documentation
- Not Equals
- Newline
- Filtering
- Give me the value or give me ZERO... no errors PLEASE!!!
That's the link to all the formulas available in google spreadsheets (hint: click on the title of this very section). It is a pain to find just going to google help (strange....). This first tip is golden! Now you know where the formula list is.
There are two ways to do not equals:
- Use the symbol: <>
- Use the function: NE()
The first one can be used in calls like the following:
- =IF(A2<>"SKIP","Fanfare!","--")
- =SUMIF(B4:B8,"<>2")
That first one will print "Fanfare!" in its cell if the contents of A2 is not equal to "SKIP". That second one will will sum the contents of the cells in the range B4 to B8 that are not equal to 2.
Pretty sweet huh?
There are two ways to create a newline within a cell:
Filtering is pretty dang sweet. It allows you to select specific cells of one column, based on criteria against another column. And if you want to get even more crazy, you can add more column/criteria pairs. In fact, you can even do rows, or both columns AND rows (but your brain might explode... just sayin'.)
- Use: &CHAR(10)&
- Use shortcut: Ctrl+Enter
(i.e., press and hold the ctrl key and then press the enter key)
I typically use the second one.
Filtering
Filtering is pretty dang sweet. It allows you to select specific cells of one column, based on criteria against another column. And if you want to get even more crazy, you can add more column/criteria pairs. In fact, you can even do rows, or both columns AND rows (but your brain might explode... just sayin'.)
=2*sum( filter( C2:C40, C2:C40<>"#N/A") )
This formula will sum all of the values in the column C from rows 2 through 40 where the value within that cell is not equal to #N/A... and then multiply the result by 2. So if we had the following:
| C | |
| 2 | 4 |
| 3 | 2 |
| 4 | #N/A |
| 5 | 3 |
The answer would be 2*(4+2+3) = 2*9 = 18.
What filter basically does is return the values for all cells in the first range where the corresponding cell in the criteria range meets the criteria.
Let's look at another example:
=2*sum( filter( C2:C40, C2:C40<>"#N/A", D2:D40<>3) )
This formula will sum all of the values in the column C from rows 2 through 40 where the value within that cell is not equal to #N/A and the corresponding cells of column D from 2 through 40 is not equal to 3. The result is then multiplied by 2. So if we had the following:
| C | D | |
| 2 | 4 | 3 |
| 3 | 2 | 0 |
| 4 | #N/A | 2 |
| 5 | 3 | 9 |
The answer would be 2*(2+3) = 2*5 = 10.
Mull that one over a bit. But yes, filter is bad @$$.
Give me the value or give me ZERO... no errors PLEASE!!!
Answer: N( IFERROR( value ) )
This combination of functions is magical. It will return 0 if whatever you are trying to do for value bombs big time. But, if it doesn't bomb, this combo will return value.
This combination of functions is magical. It will return 0 if whatever you are trying to do for value bombs big time. But, if it doesn't bomb, this combo will return value.
Why would we want to do this? Check out the following data set:
| C | D | E | |
| 2 | a | inc | 3 |
| 3 | b | inc | 1 |
| 4 | a | dec | -2 |
| 5 | b | add | 9 |
Okay, so we've got something identified by the letter "a" and something identified by the letter "b". We also have indicators that suggest incrementing (inc), decrementing (dec), and adding (add). In this data set, inc and add mean the same thing. So to get the total amount of a particular item in column C, we need to some the values in column E where the corresponding cells in column D equal inc or add.
Whew..... got that?
Let's take the item designated as "b". If we wanted to find the total amount of it, we need two sums:
- sum of all rows in E corresponding to item "b" designated as inc
- sum of all rows in E corresponding to item "b" designated as add
So, we filter on column E where column C="b" and where column D="inc". Then we filter again except change "inc" to "add".
= sum( filter(!E:E,!C:C="b",!D:D="inc") ) + sum( filter(!E:E,!C:C="b",!D:D="add") )
This will gives us the answer 9. Great! Now, if we do the same thing for "a", the answer will be #N/A. Why? Because there are no rows in the data set for item "a" designated as add. So the filter won't return anything, and sum will throw an error. We don't actually care that there were no rows designated as add. In fact, we would just be happy if it returned 0.
BAM! That's where N( IFERROR( value ) ) swoops in like Superman and saves the day (or like Deadpool with guns blasting). If we wrap each sum method in N( IFERROR( ) ), we will get the result of 3 for "a". WIN!
There may be a simpler way. If you know it, holla at me.
