Hi,

You may already have started to become aware of the difficulty in finding the formulas you need.

You are very right. Writing Excel Formulas is an art and you need to create a system to master this art!

Don’t miss this short story…

The first spreadsheet I opened at my job, contained a novel in each cell, believe me…lines and lines of formula coding. It was Greek for me.

I thought to myself, what a monster genius of Advanced Excel formulas (I thought the author had conceived the formulas in one sitting).

Nothing was farther from the truth…

Later I thought that the Expert who had written those formulas, must have had and followed a kind of system.  I never discovered it, but I built my own system and I will reveal it to you right now…

I will show you how to start from nothing and become an expert in new functions in Excel.

Why is it so important to master Excel Functions?

 

The fact of knowing the fine details of an Excel Function means you can use them more creatively in the middle of another function or formula and achieve the results you want. The better you know your options, the more efficient you are.

 

Here is the system applied to the IF function…

 

1) Manipulate your mind

If you don’t manipulate your mind, your mind will manipulate you.

Make your mind  believe that the function is easy. Do not make a monster out of it before you even begin……

Go to the help section and explore the examples (Don’t look at the syntax). Depict in your mind what each formula does without focusing on technical stuff.

Read only the description of the formula. For example…

“The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,”Over 10″,”10 or less”) returns “Over 10” if A1 is greater than 10, and “10 or less” if A1 is less than or equal to 10.”

Another useful tactic is to write in a cell the result you want to achieve using formulas and functions. Yes, imagine the formula is there!  Always focus on the results you want, let the amazing power of your mind bring you the steps.

 

2) Eliminate hesitation
Your central nervous system is hard wired to protect you from dangerous situations. We evolved in the past because we kept aware of flooding, lightning, wild animals  –  so we searched for shelter in caves, etc.

This same mindset can work against you. When you are heavily conditioned against making mistakes (by teachers, parents, peers etc.) and interpret mistakes as dangerous.

Making mistakes is an incredibly important part of learning, growing and exploring, so long as they are not life threatening.

Once you eliminate the sense of danger from making mistakes, you will feel comfortable because your brain will not perceive them as a thread. And you will learn faster, and therefore learn more.

Here are some tips…

First create a kind of master table to make mistakes on it. For example:

Name, Sales, Bonus

Nancy, 1000,

George, 800,

William, 50,

You will improve this master table with use. So you can use it to test any function or formula.

Choose the cell C2 and insert the function, in this case =IF(

It appears the syntax: IF(logical_test, value_if_true, [value_if_false])

At this point…

On the help section, read the description of the syntax. Don’t focus on the wording, just pick an example of the syntax. For example: for the argument logical_test focus on…For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE…

Focus only on what you need to do it correctly.  You will learn the details with use.

Give what the function asks you. for example IF(A2=”Nancy” or IF(B2>500, etc.

Insert the comma (,) and specify the next argument.

If you are afraid of inserting numbers, texts, references, etc. Just do something weird so you get rid of the fear of  making mistakes. You will learn what you cannot do and this is a vital aspect of learning functions.

 

3) Split test
To gain confidence with the function, test it in several scenarios: use negatives, test opposites, use text, use numbers, etc.

Change the parameters, for example: change the formula =IF(B2>500,”Yes”,”No”) to =IF(B2<500,”Yes”,”No”), =IF(B2>1000,”Yes”,”No”), =IF(B2>0,”Yes”,”No”), etc.

Change the  inputs also, for example: see what happens when you change the values in cells…

Name, Sales, Bonus

Nancy, 0,

George, 10000,

William, Hello,

The more you know about the function behavior and the more you feel and see it, the more likely you are to use it to solve more and more problems.

 

Well, I think that’s enough for today.

Conclusion

What happens when you start creating Excel reports exactly as “End-users” want?

Your boss is most likely to say, I like the report you sent me…it allowed me to make a decision right away. Could you prepare this other one for a client?, etc…because you have provided him new insights about a key business metric, he is now confident in trusting you, knowing his life will be easier.

The fact that data comes in tabular form and the fact that we need to get access to specific figures of those tables, implies that the increase in your skills to manage tables, will give you a cutting edge advantage.

All the best

John Franco