IF Function

 

Summary

The IF function can perform a logical test and return one value for a TRUE result and another for a FALSE result. For example, to "pass" scores more than or equal to 70:

=IF(C2>=70,"Pass","Fail")


More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

Purpose

Test for a specific condition

Return value

The values you supply for TRUE or FALSE

Syntax

=IF (logical_test, [value_if_true], [value_if_false])

Arguments

·        logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.

·        value_if_true - [optional] The value to return when logical_test evaluates to TRUE.

·        value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Usage notes

Use the IF function to test for or evaluate certain conditions and then react differently depending on whether the test was TRUE or FALSE.

Nested IF statements

The IF function can be "nested". A "nested IF" refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible results. Each IF statement needs to be carefully "nested" inside another so that the logic is correct. For example the following formula can be used to assign a grade rather than a pass or fail result:

=IF(C6<70,"F",IF(C6<75,"D",IF(C6<85,"C",IF(C6<95,"B","A"))))


Note: Up to 64 IF functions can be nested.

Logical operators

When you are constructing a test with IF, you can use any of the following logical operators:

Comparison operator

Meaning

Example

=

equal to

A1=D1

> 

greater than

A1>D1

>=

greater than

or equal to

 

A1>=D1

< 

less than

A1<d1

<=

less than or

equal to

A1<=D1

<> 

not equal to

A1<>D1

IF with AND, OR

The IF function can be combined with the AND function and the OR function. For example, to return "OK" when A1 is between 7 and 10, you can use a formula like this:

=IF(AND(B2>25,B2<50),"Pass","Fail")


=IF(OR(A2="Boy",A2="Girl"),”1”, ”0”)


Notes

·        To count things conditionally, use the COUNTIF or the COUNTIFS functions.

·        To sum things conditionally, use the SUMIF or the SUMIFS functions.

·        If any of the arguments to IF are supplied as arrays, the IF function will evaluate every element of the array.

If Monday, roll back to Friday

Generic formula

=IF(WEEKDAY(date)=2,date-3,date)



Explanation

To check the weekday of a date and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions. In the example shown, the formula in B1 is

=IF(WEEKDAY(A1)=2,A1-3,A1)

How this formula works

The WEEKDAY function returns a number 1-7 that corresponds to particular days of the week. By default, WEEKDAY assumes a Sunday-based week and assigns 1 to Sunday, 2 to Monday and so on with 7 assigned to Saturday.

In this case we only want to take action if the date in question is Monday. To test, we use this expression inside the IF function:

WEEKDAY(A1)=2

If the logical expression returns TRUE, we know the date is a Monday so we subtract 3 to "roll back" to Friday. If the expression returns FALSE, we simply return the original date.

Recommended Articles

Comments

Post a Comment

Popular posts from this blog

TDS on Salary – Entry in Tally

Image
  Introduction TDS is Tax Deducted at Source. TDS is the amount of tax which is deducted by the employer from the taxpayer and is deposited to the Income Tax Department on behalf of him/her. The TDS rates are set on the basis of the age and income of different individuals. TDS or Tax Deducted at Source is a specific amount that is reduced when a certain payment like salary, commission, rent, interest, professional fees etc. is made. The person who makes the payment deducts tax at the source while the person who receives a payment/income has the liability to pay tax. It lowers tax evasion because the tax will be collected at the time of making a payment. Note: TDS is nothing but advance payment of income tax. When the TDS applied When you get your salary, TDS is deducted from your salary. Let’s for simplicity assume that the TDS on your salary is ₹20,000 and you are applying for the refund of the TDS because your income i.e. your salary is under the base limit for income tax wh
Read more

Post Dated Cheque (PDC) Management

Image
  Introduction Post Dated Cheque is an essential part of today’s business world. For a better financial planning and fund management PDC cheque is a must tool to survive in a tight financial conditions of a competitive world. A cheque in which a future date is written on its face is called Post Dated Cheque or popularly known as PDC. PDC can be issued to your suppliers and can be received from your customers. Normally Post-dated cheques are presented on the date of Cheque and booked or accounted on the same date. If you have a large number of PDC to manage then it is annoying for accountant to keep track of cheques. Tally ERP 9 take charge of the PDC Management for you and let you do a tension free accounting. You can enter post-dated cheques into receipt voucher, payment voucher and contra vouchers. Benefits of Post-dated Cheque Transactions 1.    You can enter the cheque into tally immediately after receiving or issuing it. 2.    You can mark a cheque as post-dated which wi
Read more

Personal Accounts in Tally

Image
 Introduction You can easily maintain personal accounts in Tally. To maintain personal accounts in Tally is very easy and simple. It is much faster than maintaining accounts for a company. The reason is that personal accounts are comparatively very small and involve only a few amounts of entries. Now compare that to a fully-fledged company which is selling products and includes all kinds of accounting entries and on the top of that, there is GST accounting entries too. But when you maintain personal accounts in Tally, there is generally no GST involved and you need to just focus on your balance sheet. So, let’s get started step by step. Maintain Personal Accounts in Tally When you have to maintain personal accounts in Tally, you should mainly focus on balance sheet. There is a specific reason for that. In personal account, there is no business account involved because it is your personal accounts. Therefore, there is NO need for a Profit and Loss Account. If you have a business, It
Read more