EXCEL TIP: The IF Statement made simple

One of the functions I use most often in Excel is the IF statement. This function is very powerful and to many can seem very complicated, whereas to others it is deceptively simple and its power can be underestimated.

The basic IF statement


The basic format of an IF statement is as follows:

=IF(if this is true,return this,otherwise return this)


Example:

=IF(A2>3,"Greater than three","Not greater than three")

returns the text Greater than three if the number in cell A2 is greater than 3 and Not greater than three, if it is not.

The first argument can be any expression that can be true or false, usually using =,>,<,<=,>= or <>.

Other Examples:

=IF(A3="X","Yes","No")
=IF(SUM(A2:A10)>B2,"Over Budget","Within Budget")
=IF(A2<0,0,A2)

Combining conditions


All of the above examples include only one condition, but it is possible to combine numerous conditions using the AND and OR functions.

Theses functions are formatted as follows:

=AND(Condition 1, Condition 2, Condition 3.....Condition n)
=OR(Condition 1, Condition 2, Condition 3.....Condition n)

The AND function returns TRUE if ALL of the individual conditions are true.

The OR function returns TRUE if ANY of the individual conditions are true.

They can be used in IF statements as follows:

=IF(AND(A2<=200,A2>=100),"In Range","Out if Range")
=IF(OR(A2="X",B2="X",C2="X"),"Contains X","Doesn't Contain X")

More complex decisions (Nested IF Statements)


If the decision required is more complicated, you can have IF statements within IF statements - this is called Nesting.

Example:

=IF(A2=0,"NIL",IF(A2>0,"POSITIVE","NEGATIVE"))

If A2 is 0, this will return the word NIL, however if A is not 0 the third argument is another IF statement that will return the word POSITIVE if A2 is greater than zero, otherwise it will return NEGATIVE.

And that is the IF statement. Don't forget you can still take the easy route and get your spreadsheet built for you at Spreadsheets by Email.


Click here for our our exclusive offer on Online Excel Training 

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Lessons I didn't learn until I ran my own business - Lesson 1

This post follows on from an earlier one, 5 Lessons I didn't learn until I ran my own business where I listed the top lessons I have learned since starting my own business nearly 10 years ago. I promised I would revisit them in more detail later, so here goes with Lesson 1.


Lesson 1: The salesmen and women were right. Sales must come first.


This is a tough one to swallow from a financial background. I am sure we've all at times thought that salesmen are so full of themselves, thinking that the whole business should revolve around them. They would always think that they are key to the business and we financial people are just overheads. Well, although I wouldn't go that far, there was a lot of truth in what they said.


No matter how fantastic our back-office systems and  processes are, they are still a cost. By making them better, we can reduce that cost, but not eliminate it.


As accountants we should know better than anyone,


Profit = Sales - Costs


If we are being paid our wages, then costs must be positive and it doesn't matter how much we reduce costs, profits will be negative unless sales exceed them.

Like many of us from an accounting background, I followed my instincts and set up all of the back office processes straight away. Thereby creating overheads before sales and guaranteeing early losses.

Starting over I would do it differently. Believe me, it's a lot nicer problem to have sales flooding in and a need to set up processes to account for them, than to have overheads to pay and a need for sales to meet them.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

An Introduction to working with dates in Excel

I find that many clients are quite comfortable working with financial figures in Excel, but don't know how useful Excel can be when manipulating dates.

I thought that it might be useful to provide a short post on some of the most useful functions for handling dates in Excel.

One of the simplest functions is the TODAY function, which can be used on its own or within another formula to return today's date. It has no arguments and is essentially a variable (Excel still requires empty brackets after it so that it has the same format as other functions)..

Example:

=TODAY()          will display today's date in the cell


I covered YEAR, MONTH and DAY in an earlier post. These allow you to return the year, month or day respectively (as a number).

Examples:

Where cell A1 contains the date 5th October 2010,

=YEAR(A1)   returns 2010
=MONTH(A1)   returns 10
=DAY(A1)    returns 5

WEEKDAY allows you to identify the day of the week (as a number) of any date. It has two arguments, the first being the date you wish to use and the second (optional) argument being the number 1,2 or 3 depending on how you want to number your days.

1:  Sunday=1 through to Saturday=7
2:  Monday=1 through to Sunday=7
3:  Monday=0 through to Sunday=6

If this argument is not entered then it defaults to setting 1.

Examples:

Where cell A1 contains 5th October 2010 again (a Tuesday),

=WEEKDAY(A1)   returns 3
=WEEKDAY(A1,1)   returns 3
=WEEKDAY(A1,2)   returns 2
=WEEKDAY(A1,3)   returns 1

I hope this has made you feel a little more comfortable when working with dates in Excel.

Don't forget if you want to take the even easier route, we can build your spreadsheet for you at Spreadsheets by Email.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.