Scribd
Upload
18 views

Corrected_Advanced_Excel_Formulas_with_Examples

helolo formula

Uploaded by

excellearning80
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
18 views

Corrected_Advanced_Excel_Formulas_with_Examples

helolo formula

Uploaded by

excellearning80
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 2

S.

No Category Formula
Syntax/Description
Example
1 Lookup andVLOOKUP 0 Find a product price by its code in a table.
2 Lookup andHLOOKUP 0 Find a student's marks in a row by subject.
3 Lookup andINDEX 0 Get the value at row 2 and column 3 of a table.
4 Lookup andMATCH 0 Find the position of 'Apple' in a list.
5 Lookup andXLOOKUP #NAME? Find the sales data for a product ID.
6 Lookup andOFFSET #NAME? Get the reference for a cell 2 rows down and 1 column right.
7 Logical IF 0 Return 'Pass' if marks > 40, else 'Fail'.
8 Logical IFS Err:508 Return 'A' for >=90, 'B' for >=80, etc.
9 Logical AND Err:508 Check if all conditions (A1 > 0, A2 > 0) are true.
10 Logical OR Err:508 Check if either A1 > 0 or A2 > 0 is true.
11 Logical NOT 0 Reverse TRUE to FALSE and vice versa.
12 Text CONCATEN Err:508 Combine 'First' and 'Last' into 'FirstLast'.
13 Text TEXTJOIN Err:508 Combine names with commas: John, Jane, Joe.
14 Text LEFT 0 Get the first 5 characters of a name.
15 Text RIGHT 0 Get the last 3 characters of an account number.
16 Text MID 0 Extract 3 characters starting from the 2nd.
17 Text TRIM 0 Remove extra spaces from a sentence.
18 Text LEN 0 Count the number of characters in a string.
19 Text FIND 0 Find the position of 'day' in 'Monday'.
20 Text SUBSTITUT 0 Replace 'day' with 'night' in 'Monday'.
21 Math SUMIF 0 Sum sales for 'East' region.
22 Math SUMPRODU Err:508 Calculate total revenue = Units * Price.
23 Math ROUND 0 Round 2.678 to 2 decimal places: 2.68.
24 Math ROUNDUP 0 Round 2.1 to the next whole number: 3.
25 Math ROUNDDO 0 Round 2.9 down to the whole number: 2.
26 Math MOD 0 Find the remainder of 10/3: 1.
27 Math CEILING 0 Round 11.25 up to the nearest 0.5: 11.5.
28 Math FLOOR 0 Round 11.25 down to the nearest 0.5: 11.0.
29 Math RAND 0.35179 Generate a random number between 0 and 1.
30 Math RANDBETW #NAME? Generate a random integer between 1 and 10.
31 Date and TTODAY 45640 Show today's date: 12-Dec-2024.
32 Date and TNOW 45640.3 Show the current date and time.
33 Date and TDATE 0 Create 01-Jan-2024 from year, month, day.
34 Date and TDATEDIF 0 Days between 01-Jan-2024 and 12-Dec-2024.
35 Date and TEDATE 0 Add 3 months to 12-Dec-2024.
36 Date and TNETWORKD 0 Count workdays from 01-Dec to 12-Dec.
37 Array UNIQUE #NAME? List unique products in a sales table.
38 Array SORT #NAME? Sort sales data by revenue in descending order.
39 Array FILTER #NAME? Filter sales data where region = 'East'.
40 Array SEQUENCE #NAME? Create numbers 1 to 10 in a sequence.
41 Error HandIFERROR 0 Return 'OK' or 'Error' for calculations.
42 Error HandIFNA 0 Replace #N/A with 'Not Found'.
43 Error HandERROR.TYP 0 Return error type (e.g., #DIV/0! as 2).
44 Statistical AVERAGEIF 0 Average sales for 'East' region.
45 Statistical COUNTIF 0 Count orders where region = 'East'.
46 Statistical LARGE 0 Find the 2nd highest revenue.
47 Statistical SMALL 0 Find the smallest value in a dataset.
48 Financial PMT 0 Calculate monthly loan payment: $500.
49 Financial NPV Err:508 Calculate the net present value of cash flows.
50 Financial IRR 0 Calculate the internal rate of return.

You might also like