-
(Logical)
=MOD(column(),2)
- makes data alternate column colors without converting the data to a Table.
-
(Logical)
=MOD(row(),2)
- - makes data table alternate row colors without converting the data to a Table.
- - This formula is modifying the row by dividing nothing by 2 which will then cause it to format theremainder (even rows) to the Set Format of your choosing. (the row is anested formula)
- - Do this for multiples of 3or others.
-
(Logical)
=AND
- =AND(logical 1, [logical 2])
- =AND(H5<2000,H5>30000)
- Checks whether all arguments are TRUE and returns TRUE if ALL arguments are TRUE.
-
(Logical)
=OR
- =OR(logical 1, [logical 2])
- =OR(H5<20000,H5>25000)
- - Checks whether any arguments are TRUE, and returns TRUE or FALSE.
- - FALSE only if ALL argumentsare false.
-
(Logical)
=NOT
- - Changes FALSE to TRUE orTRUE to FALSE
- - If Cell I3 has a value of 89 and you put in the formula =NOT(I3=89) your answer will be FALSE because it is the opposite answer.
-
(Logical)
=NOT
- - Changes FALSE to TRUE orTRUE to FALSE
- - If Cell I3 has avalue of 89 and you put in the formula =NOT(I3=89) your answer will beFALSE because it is the opposite answer.
-
(Logical)
=IF
- =IF(_______,________,_______)
- (logic test, if True this displays, if False this displays)
- =if(C9>70,"WatchOut!","You'reOK")
If a salesman sells morethan 5 items he is paid $30 per items, else he will be paid $20 peritems: =if(C10>5,30*C10,20*C10)
-
(Logical)
=IFERROR
- Use the IFERROR function to trap and handle errors in a formula
-
(Text)
=UPPER
- =UPPER(text)
- - makes text uppercase
- - makes the changed text a formula (must hit Copy, andthen go to Paste --> Values to turn to text.)
-
=LOWER
- (Text)
- =LOWER(text)
- - makes text lowercase
- - makes the changed text a formula (must hit Copy, and then go to Paste --> Values to turn to text.)
-
(Text)
=PROPER
- =PROPER(text)
- - makes text sentence format, initial word is capped
- - makes the changed text a formula (must hit Copy, and
- then go to Paste --> Values to turn to text.)
-
(Text)
=TRIM
- - deletes excess spaces before, between, and after words in a cell
- - makes the changed text a formula (must hit Copy, andthen go to Paste --> Values to turn to text.)
-
(Text)
=SUBSTITUE
- - substitutes text
- =SUBSTITUTE(Cell with the text that needs substituting, "text to be replaced", "text to replace with") remember to use "" for all text!
-
=CONCATENATE
- =CONCATENATE(text1, [text2]...)
- - Join several text strings into one text string.
- - Join Text From Different Cells: =A1&A2
- - Join TextFrom Different Cells with a Space: =A1&” “&A2 (all text in a formula must be in quotes.Spaces are considered text.)
-
-
=LEFT - Extracts text starting at the left of the cell
- =LEFT (A1,3) (Cell with the textyou want to extract from, number of letters you want to extract)
- - SPACES ARE COUNTED AS TEXT!!!
|
|