Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.If you're new to formulae, or are keen to get your toes wet, here are a few tips to get you started.
Every formula should start with an = sign.
=A1+B2
=MAX($A:$A)
Beyond basic arithmetic, every formula has a standard syntax:
=FORMULA(ARGUMENTS)
FORMULA is the name of the formula being used; ARGUMENTS are a set of one or more conditions, references or values, depending on the formula being used. The arguments are always encased in round brackets.
Next up: text. Text references must always be contained within double-quotes. References to numbers should not be contained in quotes, nor should cell references, nor the names of the formulae themselves.
=IF(A1="New York","Yes",5)
This formula will check whether cell A1 contains the text "New York". If it does, then it will return the text "Yes". If not, it will return the number 5. The IF formula is not encased within quotes, nor is the number 5. But New York and Yes are both snippets of text, so they need to be wrapped in quotes.
Formulae are made up of arguments, and each pair of arguments is separated with a comma. Traditionally, you weren't allowed to put a space in a formula (unless it was part of a text string, as with New York above). More recent versions allow you to put a space after each comma, mainly to make them more readable. Purists might sneer at this. But if it helps you, then do it.
Most formulae involve references to other cells in the workbook. If you don't specify which sheet the cell reference refers to, then it defaults to refer to the cell within the sheet in which the formula has been written. So if you're in Sheet1 and refer to B25, then this will refer to cell B25 within Sheet1. But there's also a way of referencing cells in other sheets.
Sheet2!B9: this will refer to cell B9 within the worksheet named Sheet2
'Business Analysis'!B9: this will refer to cell B9 within the worksheet named Business Analysis
The latter example above demands single quotes around the sheet name because the sheet title contains a space. Without them, Excel would get confused. Put an exclamation mark after the sheet reference and follow it immediately with the cell reference.
If you have any trouble, get in touch and I'll try to help you out.
Dan, excellent website! I would like to ask you a question for something I can't find the answer for.
I guess it would be like an if/and statement in computer programming.
This is what I have come up with so far
=IF(E73<0.02,"Vertical Tail Coefficient is too LOW - Adjust Vertical Tail Coefficient","Vertical Tail Coefficient is acceptable")
The acceptable results should fall between 0.02 and 0.05.
So, using my If statement above, if the calculated number in cell E73 is below 0.02 I will get the return "Vertical Tail Coefficient is too LOW".
If It doesn't fall below 0.02, this statement is returned "Vertical Tail Coefficient is acceptable".
I want to add one more line, and this is where I'm having trouble. I want anything OVER 0.05 to return a statement saying, "Vertical Tail Coefficient is too HIGH".
For the returns that are too low and too high, I would like for the cell where the statement is printed to be RED. When the return is within the limits of 0.02 and 0.05 I want the cell to be green where the statement is made.
Any help with this would greatly be appreciated.