The most effective "three little words".......

Meet Sam. Sam has a dilemma. He has met the woman of his dreams - lets call her Suzie. They have had a delightful couple of years together, and Sam can picture himself spending the rest of his life with her. He wants to get down on one knee, declare his undying love, and beg for her hand in marriage (yes, soppy for an Excel geek, I know).


So what's the dilemma? Well, Suzie is already turning into a bridezilla. She has been anticipating this day, and presents him with a list of six rings, along with their basic characteristics. When Sam asks her, in some trepidation, what they cost, she airily replies, "about €5000". Sam is a bit of an Excel fan himself so does up this little spreadsheet, to help him budget:

Ah Sam, bless him. He hasn't done his research. He doesn't know about the 4Cs of diamond buying (and for ease in this example, we'll only concentrate on one). He doesn't realise that bigger rocks mean more money, and that platinum is quite a pricey commodity.

So how can we help him? We use the three little words that really mean something...IF, AND and OR.

Now, a couple of caveats before we go further....
a) I am not a jeweller and am sadly not in the habit of buying diamond rings all that often, so these prices are completely made up, and are not particularly logical. Its all for the sake of example....
b) There are other ways of working this problem out - vlookups, modelling tools etc. I am going to do the following examples purely to illustrate how to use IF, AND and OR in formulas.

Lets assume that the base price of €5k applies to a 0.5 carat diamond on a gold band. Sam gets told by his friendly jeweller that all platinum rings incur a 15% markup to cover the cost of the metal. Easy way for Sam to update his sheet? An IF statement.

An IF statement basically says "Is this statement true? If yes - do A, if not - do B". So our formula checks to see whether the metal in column C is platinum, and if it is, it takes the base price and adds the mark up of 15%. If it isn't platinum, the formula just picks up the base price as is.


So Sam thinks he is sorted, but alas no. Our friendly jeweller tells him that a 1 carat diamond carries a hefty markup of 60%, but for this price, they will exclude the additional cost of a platinum band. What now? Well, we use 2 IF statements in one...like so:

This formula uses the first IF statement to check whether the diamond is 1 carat. If it is, it adds on 60%. If the diamond is not 1 carat, then instead of going to the base price, it then checks if the metal is platinum, and adds 15%. This is the 2nd IF statement.

Note: - It is ESSENTIAL to get the logic right in these formulas. If you had checked the metal type first, and you had a platinum ring with a 1 carat diamond, your formula would only show a 15% markup.

You can nest an IF statement quite a few times in one formula, but beware. It gets very complicated and if you find you have 10 or 15 IF statements in one formula, you might want to step back and see if there is an easier way.

Anyway, lets get back to Sam. He heads to another jeweller who has a different pricing system. This jeweller tells Sam that any diamond equal or greater to 0.7 carats, on a band of platinum, has a markup of 30%.

Here we have just nested an AND statement into an IF statement. So instead of one statement needing to be true, BOTH statements need to be true - the diamond must be greater or equal to 0.7, AND the band must be platinum - for us to do step A, which is add on 30%. If BOTH statements are not true, then the formula will revert to the base price.

Now, clearly this pricing is not quite logical. So lets say that if EITHER the band is platinum, or the diamond is greater than or equal 0.7 carats, it gets a 30% markup. We can now use a nested OR statement:

This is the same as the nested AND statement, except in an OR statement, only ONE of the criteria needs to be true for the formula to implement step A.

So there you have it, my favourite 3 little words. Well,when it comes to Excel anyway. Obviously these formulas can get much more complicated, as you can nest away to your hearts content and make things as complicated as you would like. I just wanted to try give you a basic idea of the logic behind these simple, but oh so useful, statements.

For some more ideas on how to use these statements, why not sign up to my newsletter? Just click on this link and fill in the details...

http://www.excelangels.ie/faqs_and_weekly_tips_8.html

Happy Spreadsheeting!