Hal shaqo oo Excel ah oo aan xoogaa ku isticmaalo qaacidooyinkayga waa shaqada IF . Shaqada IF waxaa loo isticmaalaa in lagu tijaabiyo xaalad macquul ah oo soo saarta laba natiijo oo kala duwan iyadoo ku xiran haddii xaaladda macquulka ah ay soo noqoto RUN^(TRUE) ama BEEN^(FALSE) .

Aynu tusaale ahaan u isticmaalno shaxda iibka talefanka gacanta ee hoose. Waxaad soo dejisan kartaa faylka tusaalaha halkan .

HADDII Ay Ku Shaqeyso Xaalad Keliya^{(IF Function with Single Condition)}

Tixgeli xaalad meesha aad u baahan tahay si aad u xisaabiso Kharashka Komishanka^{(Commission Fee)} ee saf kasta oo iib ah, taas oo ku xidhan meesha iibka lagu sameeyay ( Saraf D^{(Column D)} ). Haddii iibka lagu sameeyay USA , Khidmadda Komishanku^{(Commission Fee)} waa 10%, haddii kale meelaha soo hadhay waxay yeelan doonaan khidmad komishanka^{(Commission Fee)} 5%.

Qaabka ugu horreeya ee aad u baahan tahay inaad geliso Cell F2^{(Cell F2)} waa sida hoos ku muuqata:

=IF(D2="USA", E2*10%, E2*5%)

Burburinta caanaha:

1. =IF( - The “=” waxay muujinaysaa bilawga caanaha unugga iyo HADDII^(IF) waa shaqada sare ee aan isticmaaleyno.
2. D2=”USA” – Tijaabada macquulka^(Logical) ah ee aanu samayno (tusaale ahaan haddii xogta ku jirta tiirka D2 ay tahay USA ).
3.  E2*10% - Natiijooyinka lagu soo celin doono qaacidada haddii natiijada tijaabada macquulka ah ee bilowga ah ay tahay RUN ^(Result)(^(TRUE) ie qiimaha tiirka D2 waa USA ).
4. E2*5% - Natiijooyinka waxaa lagu soo celin doonaa qaacidada haddii natiijada tijaabada macquulka ah ee bilowga ah ay tahay BEEN ^(Result)(^(FALSE) tusaale qiimaha tiirka D2 ma aha ^(NOT) USA ).
5. ) - xidhitaan^(Closing) xidhidh oo tilmaamaya dhamaadka qaacidada.

Markaa waxaad koobi ka dhigi kartaa qaacidada Unugga F2^{(Cell F2)} ilaa safafka intiisa kale ee ku jira Tiirka F^{(Column F)} waxayna xisaabin doontaa Kharashka Komishanka^{(Commission Fee)} ee xariiq kasta, ha ahaato 10% ama 5% waxay ku xidhan tahay haddi tijaabada^(IF) macquulka ahi ku soo noqoto RUN^(TRUE) ama BEEN^(FALSE) mid kasta saf.

HADDII Ay Ku Shaqeyso Xaalado Badan^{(IF Function with Multiple Conditions)}

Maxaa dhacaya haddii xeerarku ay yara adag yihiin halka aad u baahan tahay inaad tijaabiso wax ka badan hal xaalad oo macquul ah iyadoo natiijooyin kala duwan lagu soo celiyay xaalad kasta?

Excel wuxuu leeyahay jawaab tan! Waxaan isku dari karnaa dhowr hawlood oo IF ah oo ku dhex jira isla unug, taas oo mararka qaarkood loo yaqaanno Nsted IF^{(Nested IF)} .

Tixgeli dhacdo la mid ah halka Guddiyadu^{(Commissions)} ay kaga duwan yihiin Goob Iibka^{(Sales Location)} kasta sida hoos ku qoran:

• USA 10%
• Australia 5%
• Singapore 2%

Unugga F2^{(Cell F2)} (kaasoo hadhow lagu koobiyeynayo inta ka hartay safafka isla tiirka F), geli qaacidada sida soo socota:

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))

Burburinta caanaha:

1. =IF( - Bilowga^(Beginning) qaacidada iyadoo la adeegsanayo bayaan IF
2. D2=”USA” – Tijaabada ugu horeysa ee^(First) macquulka ah ee aan samayno (tusaale ahaan haddii xogta ku jirta tiirka D2 ay tahay USA ).
3.  E2*10% - Natiijooyinka lagu soo celin doono qaacidada haddii natiijada tijaabada macquulka ah ee bilowga ah ay tahay RUN ^(Result)(^(TRUE) ie qiimaha tiirka D2 waa USA ).
4. IF(D2=”Australia”,E2*5%,E2*2%) _ _^{(Excel IF)} _ _^(FALSE) _ _ ^(NOT) _^(USA) _ Tani waa isku mid la mid ah " HADDII Function with Single Condition"^{(IF Function with Single Condition”)} hore looga hadlay maqaalkan halkaas oo haddii qiimaha on Cell D2 waa Australia , natiijada E2*5% waa la soo celin doonaa. Haddii kale, haddii qiimaha uusan ahayn Australia , shaqadu waxay soo celin doontaa natiijada E2*2%.
5. ) - Xidhitaanka xidhitaanka^(Closing) oo tilmaamaya dhammaadka qaacidada ee shaqada ugu horreysa ee IF .

Sida Excel uu u qiimeyn doono qaacidada bidix ilaa midig, marka imtixaan macquul ah la buuxiyo (tusaale D2=“USA”, shaqadu way joogsan doontaa oo soo celin doontaa natiijada, iyada oo iska indha tireysa imtixaan kasta oo macquul ah ka dib (tusaale D2=“Australia” . )

Markaa haddii imtixaanka macquulka ah ee ugu horreeya uu soo celiyo BEEN^(FALSE) (ie, goobta maaha USA ), way sii wadi doontaa in la qiimeeyo imtixaanka labaad ee macquulka ah. Haddii imtixaanka macquulka ah ee labaad uu soo celiyo BEEN^(FALSE) sidoo kale (ie meeshu maaha Australia ), uma baahnid inaan tijaabino intaa ka badan sababtoo ah waxaan ognahay qiimaha kaliya ee suurtogalka ah ee Cell D2 waa Singapore markaa waa inay soo celisaa natiijada E2*2% .

 Haddii aad doorbidayso caddayn, waxaad ku dari kartaa imtixaanka saddexaad ee macquulka ah IF(D2=”Singapore”, “value if TRUE” , “value if FALSE”) . Sidaa darteed, qaacidada dheer ee buuxa waa sida hoos ku cad:

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,IF(D2="Singapore",E2*2%)))

Sida hore loo sheegay, kuwa kor ku xusan waxay soo celin doonaan isla natiijada qaacidada hore ee aan haysanay.

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))

Talooyin degdeg ah^{(Quick Tips)}

• Mid kasta oo ka mid ah IF ( shaqo, waxaa loo baahan yahay in la furo oo xidho xidhitaan wareeg ah. Marka ay jiraan saddex hawlood oo IF ah sida ku xusan mid ka mid ah tusaalooyinka kore, qaacidada waxay u baahan doontaa saddex xirmo "))" , mid kasta oo calaamadeynaya dhammaadka furitaan u dhiganta IF ( bayaanka.
• Haddii aynaan qeexin natiijada labaad ee imtixaanka macquulka ah (marka imtixaanka macquulka ah uu keeno BEEN^(FALSE) ), qiimaha caadiga ah ee Excel wuxuu noqon doonaa qoraalka "BEEN". ^{(“FALSE”.)}Markaa formula =IF(D2=”USA”,E2*10%) waxay soo celinaysaa qoraalka “BEEN”^{(“FALSE”)} haddi D2 aanu ahayn “USA” .
• Haddii aad leedahay dhowr imtixaan oo macquul ah oo kala duwan mid kasta oo leh natiijadiisa gaarka ah, waxaad isku dari kartaa/buulka shaqada IF dhowr jeer, midba midka kale, oo la mid ah tusaalaha kore.

How to Use If and Nested If Statements in Excel

One Excel function that I use quite a bіt in my formulas is the IF function. The IF function is used to test a logical condition and produce two different results depending on whether the logical condition returns TRUE or FALSE.

Let’s use the mobile phone sales table below as an example. You can download the example file here.

IF Function with Single Condition

Consider a scenario where you need to calculate the Commission Fee for each sales row, depending on where the sales was made (Column D). If the sales was made in the USA, the Commission Fee is 10%, otherwise the remaining locations will have Commission Fee of 5%.

The first formula that you need to enter on Cell F2 is as shown below:

=IF(D2="USA", E2*10%, E2*5%)

Formula breakdown:

1. =IF( – The “=” indicates the beginning of a formula in the cell and IF is the excel function that we are using.
2. D2=”USA” – Logical test that we perform (i.e. if data in column D2 is USA).
3.  E2*10% – Result that will be returned by the formula if the initial logical test results in TRUE (i.e. value in column D2 is USA).
4. E2*5% – Result that will be returned by the formula if the initial logical test results in FALSE (i.e. value in column D2 is NOT USA).
5. ) – Closing bracket indicating the end of the formula.

Then you can copy down the formula from Cell F2 to the rest of the rows in Column F and it will calculate the Commission Fee for each line, either by 10% or 5% dependent on whether the IF logical test returns TRUE or FALSE on each row.

IF Function with Multiple Conditions

What if the rules were a bit more complicated where you need to test for more than one logical condition with different results being returned for each condition?

Excel has an answer to this! We can combine multiple IF functions within the same cell, which is sometimes known as a Nested IF.

Consider a similar scenario where the Commissions are different for each Sales Location as below:

• USA 10%
• Australia 5%
• Singapore 2%

In Cell F2 (which later will be copied to the rest of the rows in the same column F), enter the formula as follow:

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))

Formula breakdown:

1. =IF( – Beginning of the formula using an IF statement
2. D2=”USA” – First logical test that we perform (i.e. if data in column D2 is USA).
3.  E2*10% – Result that will be returned by the formula if the initial logical test results in TRUE (i.e. value in column D2 is USA).
4. IF(D2=”Australia”,E2*5%,E2*2%) – second Excel IF statement that will be assessed if the initial logical test resulted in FALSE (i.e. value in column D2 is NOT USA). This is a similar syntax of “IF Function with Single Condition” discussed earlier in this article where if value on Cell D2 is Australia, the result of E2*5% will be returned. Otherwise, if the value is not Australia, the function will return result of E2*2%.
5. ) – Closing bracket indicating the end of the formula for the first IF function.

As Excel will assess the formula from the left to the right, when a logical test is met (e.g. D2=“USA”, the function will stop and return the result, ignoring any further logical test after (e.g. D2=“Australia”.)

So if the first logical test returns FALSE (i.e. location is not USA), it will continue to assess the second logical test. If the second logical test returns FALSE as well (i.e. location is not Australia), we do not need to test further as we know the only possible value on Cell D2 is Singapore hence it should return a result of E2*2%.

 If you prefer for clarity, you can add the third logical test IF(D2=”Singapore”, “value if TRUE” , “value if FALSE”). Therefore, the full extended formula is as shown below:

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,IF(D2="Singapore",E2*2%)))

As mentioned earlier, the above will return the same result as the initial formula that we had.

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))

Quick Tips

• For every single IF( function, there needs to be an opening and closing round bracket. When there are three IF functions as per one of the examples above, the formula will need three closing brackets “)))”, each marking the ending of a corresponding opening IF( statement.
• If we do not specify the second outcome of the logical test (when the logical test resulted in FALSE), the default value assigned by Excel will be the text “FALSE”. So formula =IF(D2=”USA”,E2*10%) will return the text “FALSE” if D2 is not “USA”.
• If you have several different logical tests each with its own different outcome, you can combine/nest the IF function multiple times, one after another, similar to the example above.

Related posts

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Sida looga saaro Gridlines gudaha Excel

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• Sida loo xarfo xarfaha Excel

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• 4 siyaabood oo loo isticmaalo calaamadda jeeg ee Excel

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Sida Loo Sameeyo Shaxda Pivot Fudud ee Excel

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel

• Sida Looga Saaro Safka Kooban Ee Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• Isticmaal kiiboodhka si aad u bedesho dhererka safka iyo ballaca tiirka gudaha Excel

• Sida loo xisaabiyo Z-Score gudaha Excel