Windows

Excel IF Formulas – An Introduction

Understanding Excel’s IF() function is essential to using Excel for anything more than the most rudimentary spreadsheets.

This article demonstrates how to use the IF function to create smart Excel Formulas that can handle real-world requirements.

You can download the example discussed in the article here.

The expense of guests

Our example revolves around the weekly expense report of one of the sales representatives working at the (fictional) XYZ Widget Company:

The XYZ accounting department will reimburse 50$ for any day a salesperson had a visitor. This means that for some of the days of the week in question, Ryan should be reimbursed, and for some days he shouldn’t.

IF to the rescue

As we don’t want to check each day’s record by hand, we’ll use the IF function to test the ‘Entertaining Guest’ column.

We do this by:

Selecting cell C4
Typing in =IF(B4=”yes”,50,0)
Pressing Enter

Now, cell C4 will contain 50$, as a guest was entertained on the 22^nd of November:

What just happened?

Before filling in the rest of the column, let’s break down the formula to understand how it works.

The IF formula has three parts: the logical expression, the TRUE output, and the FALSE output; each part is separated from the next by a comma. Excel evaluates the logical expression: if it’s true, the if function will output the TRUE output expression; if it’s false, the if function will output the FALSE output expression.

In our case, the logical expression is B4=”yes”. In the sample, cell B4 does indeed contain the string “Yes” (for the purists among us, we’ll note that the equal sign in Excel will evaluate strings with difference capitalization or case anywhere as the same string, thus “Yes” is equal to “yes”; for an exact comparison, use the EXACT() function).

The TRUE output expression in our sample is 50, so if the logical expression evaluates to true (as happens in row 4), the output of the IF function is 50 (as is seen in the sample). This represents the 50$ which will be reimbursed when the salesperson entertains a visitor.

The FALSE output expression is 0, which is the amount of money reimbursed when there’s no visitor.

To sum up our whole IF expression, it amounts to “IF there was a guest – Yes in column B – Output 50, otherwise, output 0”.

Expanding to the whole

Now we need to use the same formula for the rest of the week. To do that, we’ll use the Cell fill handle to copy the formula to the other cells in the column. This is done in to following manner:

We’ll grab the cell’s handle using the mouse
Then, we’ll drag the rectangle to cover cells C4 to C10
And finally, we’ll let go of the mouse button

Excel will copy the IF function from cell C4 to cells C5 through C10, changing B4 to B5 through B10 in the process:

The result is: Wherever there’s a “Yes” in column B, column C will contain 50$, and where there’s “No”, it will contain 0: Just what we wanted.

Summary

The IF function is THE most important component of Excel formulas, It has the power to transform your Excel sheets from simple calculators to smart tools that react intelligently to data you enter into them.

Related Topics:Excel office tutorials windows

Comments

Windows

How Important It Is To Allow Windows Updates To Install Automatically

Surely there isn’t a computer user around the globe who hasn’t encountered a windows update pop-up. We’ve all come across it unless you’ve always been a Mac user. Mostly people ignore these updates and think of them as something not too important. Eventually after having denied an update request for the umpteenth time, users resolve is to disable the automatic updates feature and instead handle updates manually.

But, these updates aren’t unimportant, one major reason is the security issue that it can cause if ignored, and surely no one would like their computer to be an easy target for hackers; that’s not all and there is more to it than security.

windows updates

Therefore, we’ll discuss the factors which will enlighten you regarding how important it is to allow windows updates to install automatically?

Following are the factors which explain the importance of automatic updates:

1. Security Updates

Microsoft keeps improving any bugs and issues reported in its operating system. Therefore, patches are routinely released to fix these issues. After a patch is publicly released it becomes apparent where the loophole is; in such a circumstance quick action must be taken before hackers can make their move to unfairly benefit from the problem. If during this time you keep your updates on manual installation, then you’re providing hackers a golden chance to create a huge issue for yourself. Thence, automatic updates are important and essential, so that hackers can be prevented from harming your computer. Also, Microsoft adds malware definitions to Windows defender and Microsoft essentials; so if a virus hits your system, you’d surely want it to be detected before it infects and corrupt your system.

2. General Updates and Bug fixes

Whilst the most important part of Windows updates is related to security, there are also general issues of Windows operating system which are required to be fixed. Updates are also made to resolve bugs, windows has a complex coding and possibility of several bugs is always there. Therefore, these bug fixes can help in removing annoying issues and aid in stabilizing the operating system; as speed can also be affected by bugs and not necessarily by a virus or malicious file.

3. New Feature updates

Windows updates also introduce new features at times alongside the bug fixes and security patches. For instance, Internet Explorer is updated. However, we know that users rarely prefer using Internet Explorer, still these updates can come handy in times of dire need. Also, when support and patches for an older version are ceased, then the unfixed loopholes can pose a threat to your computer; just as it was mentioned earlier, security problems are identified on the regular basis in various parts of the Windows operating system, including ActiveX and .NET Framework, alongside the main platform.

Final Verdict

All in all, I’d conclude this by suggesting to enable auto updates. Previously people had faced issues with auto updates, like blue screen error or simply disliked it because by the time one came back from a short break, the windows update auto rebooted a computer within ten minutes and therefore, all important work on one’s PC used to vanish into thin air unsaved; this made them stop it altogether, but well gone are the days when that happened, and now that you are aware of its importance, you shan’t delay updates to refrain from being reminded by the continuous pop-ups. How important it is to allow automatic updates can also be defined by the fact that auto updates can no longer be disabled now; in windows 8 and above, windows updates may be delayed for almost 3 days before they finally install, but disabling them isn’t a choice.

Guides

Send Text Messages from Windows 10 Through Cortana

Microsoft has a cool feature up its sleeves! A feature that will allow users to send text messages from Windows 10 through Cortana, will be released soon by Microsoft. This is a good news for Windows users who find it inconvenient to switch between their mobile phone and desktop while at work. But this new feature integrated in Cortana will make it easier for users to tackle their calls and texts from their desktop.

Those who aren’t using Windows 10 on their desktop or mobile phone device, might not be familiar with the name. Cortana is a virtual assistant, similar to Siri which was introduced by Apple. Now with Windows 10, Microsoft has introduced Cortana as a personal assistant for their users. And alongside the several tasks that “she” performs for you, one of it is this new feature of text messaging from Windows 10.

Send Text Messages from Windows 10 Through Cortana

How to send text messages from windows 10 through Cortana?

Well, there are two ways to send text messages from Windows 10; whenever you receive a message from someone or wish to send a new message, you can either type your message or simply use voice input to reply back from Cortana’s desktop app. The message will be then transferred to your mobile phone where it will be saved as part of the conversation. Alongside this Cortana also informs you of a missed call, which you can either ignore or reply to by a text message. This works by using your Microsoft account on Desktop and mobile, and syncs them both. If you have multiple computers then the missed call alerts feature can be disabled as well.

Windows 10 Text Messaging

When Cortana Windows Text Messaging will be available?

Cortana windows text messaging feature currently works on Desktop with build 10565. However, two of the new Windows 10 mobile phones which were recently launched running build 10549 do not support windows text messaging through Cortana. For this, users who are currently using Windows mobile build 10549 will need to wait until Windows 10 mobile build 10565 is released, so it is expected that by December this feature will be available for Windows users. Another question is, whether this feature will work solely with Windows 10 mobile phones, or will it also work on Cortana for Android and iOS? However, that only time will tell!

Windows

How Windows 10 Helps Increase Your Business Productivity

The launch of Windows 10 was marked with pomp, online and in the media, especially on the implications it had for home users. However, the new operating system does have a suite of unique tools to offer businesses looking to increase their productivity. In this post, we see what upgrading from your current Windows version (Windows 7 or Windows 8) to Windows 10 means for you, your business and employees.

windows10-for-business

Increased Security

A user with a Microsoft Windows 10 device enrolls in your business system as a factor, such as a smartphone. So long as the user is close to your system, they can sign into it without requiring your password. A second security factor is a pin number or a biometric scanner for fingerprint or retina scanning. This multi-factored system allows your user to log into the system from any device or computer safely and securely.

The Data Loss Prevention (DLP) Windows 10 solution separates your business data from personal data. Business and sensitive corporate materials are automatically encrypted using BitLocker. This encryption happens as soon as you create or receive files concerning your business. You have the option of defining what you consider business and what is personal, and Window 10 automatically protects and secures these vital data on your company network.

Increased Efficiency

mproved-efficiency.

For starters, Windows 10 does not require a learning curve. The operating system is firmly based on the familiar Start menu and Desktop of Windows 7. Also, the enhanced Start menu offers single click access to some of the most used files and functions on your computer. You can also setup virtual desktops easily and quickly, and switch between them depending on what aspect of your business you are working on. With separate desktops, you can enter the necessary workspace and quickly access the relevant files and programs without closing and opening numerous applications, folders and files.

Work Anywhere

virtual-work-anywhere

Microsoft’s Universal Apps model means that you can now use the same application to keep track of your office inventory from home, on the road or anywhere in the world. You can do all these without downloading different systems and integrating numerous applications of various platforms. Your database software is available to you on any device that runs Windows 10. You can now use your corporate email program, word processor or any other program from the same page on whichever Windows 10 device. This capability offers you functionality without downloading different versions for each platform of the same software. The result is fewer overhead costs and easier fixes and updates.