ALL EXCEL FORMULAS

Basic Formulas:

1. SUM: Kisi range ka total calculate karne ke liye.

   • Syntax: =SUM(A1:A10)

2. AVERAGE: Average calculate karne ke liye.

   • Syntax: =AVERAGE(A1:A10)

3. MIN: Range mein se smallest value ko return karne ke liye.

   • Syntax: =MIN(A1:A10)

4. MAX: Range mein se largest value ko return karne ke liye.

   • Syntax: =MAX(A1:A10)

5. COUNT: Sirf numeric cells count karne ke liye.

   • Syntax: =COUNT(A1:A10)

6. COUNTA: Non-empty cells ko count karne ke liye.

   • Syntax: =COUNTA(A1:A10)

7. IF: Conditions ke sath results return karne ke liye.

   • Syntax: =IF(A1>10, "Yes", "No")

8. TRIM: Extra spaces ko remove karne ke liye.

   • Syntax: =TRIM(A1)

9. LEN: Cell mein characters count karne ke liye.

   • Syntax: =LEN(A1)

10. CONCATENATE (or CONCAT): Multiple text strings ko combine karne ke liye.

    • Syntax: =CONCATENATE(A1, " ", B1)

Intermediate Formulas:

1. VLOOKUP: Data ko vertically search karne ke liye.

   • Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

2. HLOOKUP: Data ko horizontally search karne ke liye.

   • Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

3. MATCH: Kisi value ka position find karne ke liye.

   • Syntax: =MATCH(lookup_value, lookup_array, [match_type])

4. INDEX: Row aur column ke base par value return karne ke liye.

   • Syntax: =INDEX(array, row_num, [column_num])

5. CHOOSE: List mein se specific value ko select karne ke liye.

   • Syntax: =CHOOSE(index_num, value1, value2, ...)

6. LEFT/RIGHT/MID: Text ke specific parts ko extract karne ke liye.

   • Syntax:
     • LEFT: =LEFT(A1, 5)
     • RIGHT: =RIGHT(A1, 3)
     • MID: =MID(A1, 2, 5)

7. TEXT: Number ko specific format mein convert karne ke liye.

   • Syntax: =TEXT(A1, "mm/dd/yyyy")

8. AND/OR: Multiple conditions ko check karne ke liye.

   • Syntax:
     • AND: =AND(A1>10, B1<20)
     • OR: =OR(A1>10, B1<20)

9. ROUND/ROUNDUP/ROUNDDOWN: Number ko round karne ke liye.

   • Syntax: =ROUND(A1, 2) (2 decimal places)

10. NOW/TODAY: Current date ya time ko return karne ke liye.

    • Syntax:
      • NOW: =NOW()
      • TODAY: =TODAY()

Advanced Formulas:

1. ARRAY FORMULAS: Ek se zyada results ko calculate karne ke liye (use Ctrl+Shift+Enter).

   • Syntax: {=A1:A10 * B1:B10}

2. SUMIF/SUMIFS: Specific conditions ke sath sum calculate karne ke liye.

   • Syntax:
     • SUMIF: =SUMIF(range, criteria, [sum_range])
     • SUMIFS: =SUMIFS(sum_range, criteria_range1, criteria1, ...)

3. COUNTIF/COUNTIFS: Specific conditions ke sath count karne ke liye.

   • Syntax:
     • COUNTIF: =COUNTIF(range, criteria)
     • COUNTIFS: =COUNTIFS(criteria_range1, criteria1, ...)

4. IFERROR: Error ko handle karne ke liye aur custom result return karne ke liye.

   • Syntax: =IFERROR(A1/B1, "Error")

5. SUBTOTAL: Filtered data mein calculations karne ke liye.

   • Syntax: =SUBTOTAL(function_num, range)

6. XLOOKUP: New aur improved lookup function (Excel 365/2019).

   • Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

7. OFFSET: Ek range ko dynamic row aur column offset ke sath return karne ke liye.

   • Syntax: =OFFSET(reference, rows, cols, [height], [width])

8. INDIRECT: Text ke tor par di gayi cell reference ko return karne ke liye.

   • Syntax: =INDIRECT(A1)

9. TRANSPOSE: Row aur column ke data ko interchange karne ke liye.

   • Syntax: =TRANSPOSE(A1:A10)

10. NETWORKDAYS: Working days ka count return karne ke liye.

    • Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

11. RANK: List mein kisi value ka rank return karne ke liye.

    • Syntax: =RANK(A1, A1:A10)

12. DSUM/DCOUNT/DAVERAGE: Database ke andar specific conditions ke sath sum, count, ya average calculate karne ke liye.

    • Syntax:
      • DSUM: =DSUM(database, field, criteria)
      • DCOUNT: =DCOUNT(database, field, criteria)
      • DAVERAGE: =DAVERAGE(database, field, criteria)

13. TEXTJOIN: Multiple cells ko combine karne ke liye delimiter ke sath (Excel 365/2019).

    • Syntax: =TEXTJOIN(", ", TRUE, A1:A5)

14. SEQUENCE: Sequence of numbers generate karne ke liye (Excel 365).

    • Syntax: =SEQUENCE(rows, [columns], [start], [step])

15. FILTER: Data ko dynamically filter karne ke liye (Excel 365/2019).

    • Syntax: =FILTER(array, include, [if_empty])

16. UNIQUE: Unique values ko return karne ke liye (Excel 365/2019).

    • Syntax: =UNIQUE(array, [by_col], [exactly_once])

17. LET: Variables ko define karne aur unhe multiple bar use karne ke liye (Excel 365).

    • Syntax: =LET(name1, value1, calculation)

18. SORT/SORTBY: Data ko sort karne ke liye (Excel 365).

    • Syntax:
      • SORT: =SORT(array, [sort_index], [sort_order])
      • SORTBY: =SORTBY(array, by_array1, [sort_order1], ...)

Financial and Statistical Formulas:

1. PMT: Loan ka payment calculate karne ke liye.

   • Syntax: =PMT(rate, nper, pv, [fv], [type])

2. NPV: Net present value calculate karne ke liye.

   • Syntax: =NPV(rate, value1, [value2], ...)

3. IRR: Internal rate of return calculate karne ke liye.

   • Syntax: =IRR(values, [guess])

4. STDEV: Standard deviation calculate karne ke liye.

   • Syntax: =STDEV(A1:A10)

5. VAR: Variance calculate karne ke liye.

   • Syntax: =VAR(A1:A10)