Pages

Menu

Saturday, April 1, 2023

Explained “IF” Formula in Microsoft Excel


The IF formula is a conditional formula in Excel that allows you to check whether a certain condition is true or false and return a specific value based on the result. The syntax of the IF formula is :-

=IF(logical_test,value_if_true,value_if_false)

1.   The logical_test is the condition you want to test, which can be a logical expression, a comparison, or a cell reference that contains a value to be evaluated. For example, "A2>B2" or "C2=10".

2.   The value_if_true is the value that will be returned if the logical_test is true. This can be a number, text, formula, or cell reference. For example, "Yes" or "A2*2".

3.   The value_if_false is the value that will be returned if the logical_test is false. This can also be a number, text, formula, or cell reference. For example, "No" or "B2*3".

 

Here's an example of how to use the IF formula in Excel:

Suppose you have a column of test scores in column A and you want to assign a letter grade based on each score. You can use the following IF formula :-

=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))

This formula tests the value in cell A2 to see if it is greater than or equal to 90. If it is, it returns "A". If it is not, it moves on to the next logical test, which checks if the value in A2 is greater than or equal to 80. If it is, it returns "B". If not, it moves on to the next logical test and so on. If none of the logical tests are true, it returns "F".



List of Top 10 Advanced Excel 2019 Formulas with example

 

Here are ten advanced Excel formulas with examples:

1.   INDEX-MATCH :- =INDEX(A2:A10,MATCH("John",B2:B10,0))

This formula searches for the name "John" in the range B2:B10 and returns the corresponding value in the range A2:A10.

 

2.   SUMIFS :-

=SUMIFS(C2:C10,A2:A10,"John",B2:B10,"Red")

This formula adds up the values in the range C2:C10 that correspond to the name "John" in column A and the color "Red" in column B.

 

3.   IFERROR :-

=IFERROR(A2/B2,"Error: Divide by zero")

This formula divides the value in cell A2 by the value in cell B2, and if there is an error (such as dividing by zero), it displays the custom error message "Error: Divide by zero".

 

4.   CONCATENATE :-

=CONCATENATE("John"," ","Doe")

This formula combines the text strings "John", a space, and "Doe" into one cell.

 

5.   COUNTIFS :-

=COUNTIFS(A2:A10,"John",B2:B10,"Red")

This formula counts the number of times the name "John" appears in column A and the color "Red" appears in column B.

 

6.   AVERAGEIFS :- =AVERAGEIFS(C2:C10,A2:A10,"John",B2:B10,"Red")

This formula calculates the average of the values in the range C2:C10 that correspond to the name "John" in column A and the color "Red" in column B.

 

7.   CHOOSE :-

=CHOOSE(3,"Red","Green","Blue")

This formula returns the value "Blue" because it is in the third position in the list of values.

 

8.   VLOOKUP :-

=VLOOKUP("John",A2:B10,2,FALSE)

This formula searches for the name "John" in the range A2:B10 and returns the corresponding value in the second column (column B).

 

9.   HLOOKUP :-

=HLOOKUP("John",A2:B5,2,FALSE)

This formula searches for the name "John" in the range A2:B5 and returns the corresponding value in the second row.

 

10.                     ROUND :-

=ROUND(A2/B2,2)

This formula divides the value in cell A2 by the value in cell B2 and rounds the result to two decimal places.

Explain Full Microsoft Outlook 2013 all tabs and Groups

 

Microsoft Outlook 2013 is an email client included in the Microsoft Office collection of programmers. It is intended to help users organize their email, calendars, contacts, and tasks in a centralized location. The application is split into several tabs and groups, each with its own function. The following is a list of the major tabs and groups in Microsoft Outlook 2013:

 

1.   Home Tab :- This is the default tab in Outlook, and it contains the most commonly used features. The Home tab includes groups such as New, Delete, Respond, Move, and Tags. The New group allows users to create new emails, calendar appointments, contacts, and tasks. The Delete group contains options to delete emails, calendars, and other items. The Respond group includes features like reply, reply all, and forward. The Move group allows users to move emails to other folders, and the Tags group allows users to categorize and flag emails.

2.   Send/Receive Tab :- This tab contains options for sending and receiving emails. The Send/Receive tab includes groups such as Send/Receive, Download, and Preferences. The Send/Receive group allows users to send and receive emails, and the Download group allows users to download email headers or full messages. The Preferences group includes options for setting up email accounts, changing settings, and managing rules.

3.   Folder Tab :- This tab contains options for managing folders and their contents. The Folder tab includes groups such as New, Actions, and Favorites. The New group allows users to create new folders, and the Actions group includes options for managing folders, such as renaming, moving, or deleting them. The Favorites group includes options for adding or removing folders from the Favorites list.

4.   View Tab :- This tab contains options for customizing the appearance of Outlook. The View tab includes groups such as Current View, Arrangement, and Layout. The Current View group allows users to switch between different views, such as Inbox, Sent Items, or Calendar. The Arrangement group allows users to sort emails by different criteria, such as date, sender, or subject. The Layout group includes options for customizing the reading pane, navigation pane, and to-do bar.

5.   People Tab :- This tab contains options for managing contacts and address books. The People tab includes groups such as Current View, Actions, and Communicate. The Current View group allows users to switch between different views, such as Contacts, By Company, or By Category. The Actions group includes options for managing contacts, such as adding or editing contact information, or creating new contact groups. The Communicate group includes options for sending emails or creating meetings with contacts.

6.   Tasks Tab :- This tab contains options for managing tasks and to-do lists. The Tasks tab includes groups such as New, Manage, and View. The New group allows users to create new tasks, and the Manage group includes options for marking tasks as complete, changing task status, or setting reminders. The View group allows users to switch between different views, such as Active Tasks, Completed Tasks, or Overdue Tasks.

7.   Calendar Tab :- This tab contains options for managing calendars and scheduling appointments. The Calendar tab includes groups such as New, Share, and Tags. The New group allows users to create new appointments, meetings, or events. The Share group includes options for sharing calendars with other users, and the Tags group includes options for categorizing appointments.