Excel Advanced Course - Part 2 (Functions & Formulas)

Excel Advanced Course By Vatsal Saxena

Functions & Formulas

Type of Functions

1. Logical Functions
2. Lookup Functions
3. Text Functions
4. Date & Time Functions
5. Advanced Math & Statistical Functions
6. Array Formulas

1. Logical Functions- Ye vo functions hote hai jisme hum Excel ko do ya do se adhik conditions dete hai aur har ek condition ka Excel alag alag output deta hai.

• IF - IF function hum tab use krte hai jab humare paas koi ek logic test hota hai, sahi hone par ek value aati hai aur galat hone par dusri value hai.
• IFs - Agar kisi logical test ke natije me sahi aur galat na ho aur hume do se adhik results aane ki sambhavna lage tab hum IFs ka use krte hai.
  
• IFERROR -  Agar kisi formula ko lagane par koi error aa jaye tab hum IFERROR ka use krte hai. Aur Error ki jagah apni koi value de dete hai.
• OR - OR Function ek aisa function hai jisme hum do conditions dete hai aur unme se koi ek sahi hone par hume TRUE value milti hai.
• AND - AND Function ek aisa function hai jisme hum do condition dete hai. Aur dono ke sahi hone par TRUE ata hai.
  

2. Lookup Functions- 

• VLOOKUP- VLOOKUP ka use tab hota hai jab aapko kisi value ko vertical Range (Column wise) me dhundhna ho.
  =VLOOKUP(lookup_value, table_array, column_index_no, exact_match)
• HLOOKUP- HLOOKUP ka use tab hota hai jab aapko kisi value ko Horizontal (Row Wise) dhundhna ho.
  Imp- HLOOKUP ko tabhi sahi Result milta hai jab lookup row shorted ho, isliye lookup value ko pehle row me hona chahiye.
  =HLOOKUP(lookup_value, table_array, row_index_no., exact_match)
• XLOOKUP- Ye ek excel ka naya aur jyada flexible lookup function hai, jo VLOOKUP aur HLLOKUP ka Replacement hai, ye aapko vertical aur horizontal dono tarike se LOOKUP krne ki suvidha deta hai.
  =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, exact_match)
• INDEX- Is function ka use aap kisi range me koi ek specific value ko pane ke liye krte hai. Isme range, column aur row no. ka use hota hai. =INDEX(array, row_num, [column_num]).
• MATCH- Is function ka use aap kisi specified value ko ek range mein dhoondhne ke liye karte hain. Or uss value ki position aapko return me milti hai.

3. Text Functions-

• LEFT-  Iska use hum kisi text ke left side se letters ko alag krne ke liye krte hai. Jaise [Human] ke liye `=LEFT("Human",3)` => [Hum].
• RIGHT- Iska use hum kisi text ke right se letters ko alag krne ke liye krte hai. Jaise [Engine] ke liye `=RIGHT("Engine",3)` => [ine].
• MID- Iska use hum kisi text ke bich me se letters ko alag krne ke liye krte hai. Jaise [Important] ke liye `=MID("Important",3,4)` => [port].
• TRIM- Ye function kisi sentence me lage sabhi extra spaces ko single space me convert krdeta hai.
• CONCATENATE- Iska use hum do ya do se adhik text ko join krne ke liye krte hai.

4. Date & Time Functions-

• TODAY- Iss function ka use hum current date paane ke liye krte hai. =TODAY()
• NOW- Iss function ka current date & time paane ke liye krte hai ye automatic update hote hai. =NOW()
• EDATE- Iss Function ka use karke aap kisi specific date se ek specified number of months aage ya pichhe ka date nikal sakte hain.
• WORKDAY- Excel mein WORKDAY function ka use aap yeh calculate karne ke liye karte hai ki kisi tarikh se aage diye huye working days ke baad konsi date aayegi.
• NETWORKDAYS- Iss function ka use hum kisi date se di gyi date ke bich ke workings days nikalte hai Saturday or Sunday hta ke.

5. Advanced Math and Statistical Functions

• SUMIF & SUMIFS- Sumif functions ka use hum tab krte hai jab hume sum function kisi conditions ke sath lgana hota hai.
• COUNTIF & COUNTIFS- Countif functions ka use cells ko count krne me help krta hai jab humare pass koi conditions hote hai.
• AVERAGEIF & AVERAGEIFS- Agar humare pass koi condition hai aur hume diye gye no. ka average nikalna ho tab hum Averageif ka use krte hai.
• RANK- Agar humare pass koi no. ki list hai to rank formula se hum unko arrange krte hai.

6. Array Formulas

• SORT- Jab kisi array me hume items ko kisi kram me lgana hota hai tab hum sort formula ka use krte hai.
• FILTER- Kisi array me jab hume items ka kisi parameter me chhatna hota hai tab hum filter lgate hai.
• UNIQUE- Yadi kisi array me items repeat ho aur hume sirf ek ek baar har items ko array me likhna ho tab hum iska use krte hai.
• SEQUENCE- Jab hume koi array bnana ho jisme kuch kuch numbers ka antar ho tab hum sequence formula ka use krte hai.