A couple of weeks ago we announced our very first guestblogger Carl Stiller. Carl has a Master’s degree in economics and has many years of experience working as a controller. In our collaboration Carl will showcase real examples taken from his working life where Excel is a key tool!
Head on to Index where we have posted an introductory interview with Carl and also his first posts.

Want to read the post in Swedish? Click here

Today’s post

In today’s post, we will look a little at emojis and what I normally use the IF function for and where the limitation goes for this function.

Emojis

The use of emojis has become increasingly popular and accepted in daily communication in emails and text messages. Emojis have also crept into Excel with the shortcut; Win +.

This command brings up the keyboard for emojis. To see a complete list of emojis and codes please go to below website. What you can do is type a hexadecimal code to get the emojis you want instead of picking up the keyboard and search. The code for writing an emoji is: =UNICHAR(HEX2DEC(“1F600”)) where the second part converts the number to decimal and the first part converts the code to a visible emoji. Unfortunately, emojis are displayed in colour in Excel Online only.

http://unicode.org/emoji/charts/full-emoji-list.html

Emojis can be used to advantage when reporting to animate, for example, a presentation. You can use them to categorise individuals, food, drink or whatever. You can also use emojis in combination with VLOOKUP and IF. Emojis are also possible to add to the tab name.

If you want to use the function in diagrams, you can easily enter the categories using suitable codes.

The function also works well in Slicers:

The IF function

The IF function is a very simple but useful formula that is used to advantage for simpler calculations or logical contexts. The limitation of the formula is that it can be used for an either-or calculation. For example, if sales are over (>) SEK 100.000, it is good, otherwise it is bad.

I usually use the formula to an advantage for the following work:

• In simple reporting (see above)
• In combination with the OR function
• For quick analysis or year-end reconciliation*
• If you need to create a filter (see below)

* When I reconcile the accounting, I usually use the IF function, for example, to filter on the accounts that deviate by more than SEK 50.000 from average in the last 6 months or for instance deviate by more than 20% against forecast. See examples below and feel free to watch instructional video from Learnesy.

The IF AND function works in a similar way but means that you can put a very large number of assumptions in one and the same equation, which means that you can make more complicated calculations. I mainly use this formula for more advanced calculations or scenario analysis. I often use the formula for calculating margins or bonus calculation according to the example below. In a company where I did a consulting job, each salesperson had 3 different ways to receive a bonus and each way had at least 3 different criteria that should be achieved such as; total sales, sales of new products and margin.

Look at the lesson in Learnesys course Excel essentials, about the IF-function

The lesson is from Learnesys course Excel essentials, click on the link for more information.

https://vimeo.com/150787613

The scope of all the factors involved meant that you had to have one file per sales category and a tab for each way to receive a bonus. The IF AND function here was an excellent formula to use!

Personally, I do not use the IF AND or IF function to produce a text answer (if price> SEK 1500 = Sweater XL). I use it almost exclusively for calculations of numerical values.

The IF OR function which I use to a lesser extent works in the same way, mixing several factors into the equation. You usually use this formula if you have several options to obtain a target number. In the example below, you see this very phenomenon. I have marked in green all amounts that meet the bonus criteria, but as you can see, no person receives more than SEK 15.000 even if, in this case, Nisse meets all the alternatives.

Anecdote

When I worked at an IT company a few years ago, I created an IF AND formula in several stages to calculate license revenues based on start and end dates, contract amounts and the number of days per month. It took a long time to get the formula evaluated, but when it worked, it revolutionized the way we could make forecasts. One of my colleagues one month had a deviation against the forecast of only $1.000. She had an approximate revenue flow of SEK 40 million every month, so her deviation using the formula was only 0.03% ?.

Since I cannot give the exact example in question, I have illustrated below what I mean by several leaning

Next post

In this post, we have stated that I like working with complex IF AND formulas?. In the next post, I will present my experience of cleaning data and why it is such an important part of the work. We will look at practical examples from working life and, in my opinion, the most common things that need to be addressed. Feel free to connect with me on LinkedIn and go to Learnesy´s website for more information.

Carl Stiller In collaboration with Learnesy

Go to our shop!