study of Excel formulas

                     
                                     

                                  Excel  formulas

1) formulas of exal

CTRL X	CUT
CTRL C	COPY
CTRL V	PAST
CTRL Z	UNDO
TAB	FRONT
SHIFT TAB	BACK
DATA	DATA VALIDATION		1 LTST	2 WRITE	3 OK	ctrl t	CPU
INSERT	SHAPE SELECT		DROW	ADIT TAXT	RIGHT	CLICK HYPERLINK
VIEW	FREEZE PANES		FREEZE PANES		ctrl h	ctrl p	ctrl 0
wright and click and go anyway and prais ok	click hyperlink	rlink and go anyway and prass ok				ctrl n	ctrl h
ctrl 1	format sals		ctrl A	selact	ctrl 2	ctrl b	protect shet
ctrl F	ctrl G	ctrl L	ctrl M	ctrl O	ctrl 6	ctrl u	ctrl k

keyboard shortcuts tricks  1)Alt+tab used to program swich   2)computer tern off alt+ F4 enter   3) for  closing the program  Alt+F4 4) all tabs are minimise then press window+D 5)Ms office or exal save the press F12  file save ctrl+ S ms office ctrl N 6) Google tabs open ctrl+T 7) browser history open delete ctrl+H ctrl+shift+ delete  8) download menu= ctrl+ J,save ctrl+ D  9) ctrl shift t = website tab recover  10) delete file recovery ctrl z delete file ctrl y

Complete Excel Formulas Reference

Basic Excel Formulas

Formula	Description	Example
=SUM(number1, [number2], ...)	Adds all numbers in a range of cells	=SUM(A1:A5)
=AVERAGE(number1, [number2], ...)	Calculates the average of numbers	=AVERAGE(B2:B10)
=COUNT(value1, [value2], ...)	Counts cells with numbers	=COUNT(C1:C20)
=MAX(number1, [number2], ...)	Returns the largest value	=MAX(D2:D100)
=MIN(number1, [number2], ...)	Returns the smallest value	=MIN(E1:E50)
=IF(logical_test, [value_if_true], [value_if_false])	Performs conditional tests	=IF(A1>10, "Yes", "No")

Text Formulas

Formula	Description	Example
=CONCATENATE(text1, [text2], ...)	Combines text from multiple cells	=CONCATENATE(A1," ",B1)
=LEFT(text, [num_chars])	Extracts characters from the left	=LEFT(A1, 5)
=RIGHT(text, [num_chars])	Extracts characters from the right	=RIGHT(A1, 3)
=MID(text, start_num, num_chars)	Extracts characters from the middle	=MID(A1, 3, 2)
=LEN(text)	Returns the length of text	=LEN(A1)
=TRIM(text)	Removes extra spaces	=TRIM(A1)
=UPPER(text)	Converts text to uppercase	=UPPER(A1)
=LOWER(text)	Converts text to lowercase	=LOWER(A1)
=PROPER(text)	Capitalizes first letters	=PROPER(A1)

Date and Time Formulas

Formula	Description	Example
=TODAY()	Returns current date	=TODAY()
=NOW()	Returns current date and time	=NOW()
=DATE(year, month, day)	Creates a date from year, month, day	=DATE(2023, 12, 25)
=DAY(date)	Extracts day from date	=DAY(A1)
=MONTH(date)	Extracts month from date	=MONTH(A1)
=YEAR(date)	Extracts year from date	=YEAR(A1)
=DATEDIF(start_date, end_date, unit)	Calculates difference between dates	=DATEDIF(A1,B1,"d")
=WORKDAY(start_date, days, [holidays])	Returns a workday in future/past	=WORKDAY(A1,10)

Lookup and Reference Formulas

Formula	Description	Example
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])	Vertical lookup	=VLOOKUP(A1,B1:C10,2,FALSE)
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])	Horizontal lookup	=HLOOKUP(A1,B1:Z2,2,FALSE)
=INDEX(array, row_num, [column_num])	Returns value from array	=INDEX(A1:C10,2,3)
=MATCH(lookup_value, lookup_array, [match_type])	Returns position of item	=MATCH("Apple",A1:A10,0)
=INDIRECT(ref_text, [a1])	Returns reference specified by text	=INDIRECT("A"&1)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])	Modern lookup (Excel 365)	=XLOOKUP(A1,B1:B10,C1:C10)

Logical Formulas

Formula	Description	Example
=AND(logical1, [logical2], ...)	Returns TRUE if all arguments are TRUE	=AND(A1>10,B1<20)
=OR(logical1, [logical2], ...)	Returns TRUE if any argument is TRUE	=OR(A1>10,B1<5)
=NOT(logical)	Reverses logical value	=NOT(A1>10)
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)	Multiple IF conditions (Excel 2019+)	=IFS(A1>90,"A",A1>80,"B",A1>70,"C",TRUE,"F")
=SWITCH(expression, value1, result1, [default_or_value2, result2], ...)	Evaluates expression against values (Excel 2019+)	=SWITCH(A1,1,"One",2,"Two","Other")

Math and Trigonometry Formulas

Formula	Description	Example
=ROUND(number, num_digits)	Rounds number to digits	=ROUND(A1,2)
=ROUNDUP(number, num_digits)	Rounds number up	=ROUNDUP(A1,0)
=ROUNDDOWN(number, num_digits)	Rounds number down	=ROUNDDOWN(A1,0)
=SUMIF(range, criteria, [sum_range])	Adds cells that meet criteria	=SUMIF(A1:A10,">10",B1:B10)
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)	Adds cells with multiple criteria	=SUMIFS(C1:C10,A1:A10,">10",B1:B10,"<20")
=SUMPRODUCT(array1, [array2], ...)	Multiplies arrays and returns sum	=SUMPRODUCT(A1:A10,B1:B10)
=MOD(number, divisor)	Returns remainder after division	=MOD(10,3)
=POWER(number, power)	Returns number raised to power	=POWER(2,3)
=SQRT(number)	Returns square root	=SQRT(16)

Statistical Formulas

Formula	Description	Example
=COUNTIF(range, criteria)	Counts cells that meet criteria	=COUNTIF(A1:A10,">10")
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)	Counts cells with multiple criteria	=COUNTIFS(A1:A10,">10",B1:B10,"<20")
=AVERAGEIF(range, criteria, [average_range])	Average of cells that meet criteria	=AVERAGEIF(A1:A10,">10")
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)	Average with multiple criteria	=AVERAGEIFS(C1:C10,A1:A10,">10",B1:B10,"<20")
=MEDIAN(number1, [number2], ...)	Returns median of numbers	=MEDIAN(A1:A10)
=MODE(number1, [number2], ...)	Returns most frequent number	=MODE(A1:A10)
=STDEV(number1, [number2], ...)	Standard deviation (sample)	=STDEV(A1:A10)
=STDEVP(number1, [number2], ...)	Standard deviation (population)	=STDEVP(A1:A10)

Financial Formulas

Formula	Description	Example
=PMT(rate, nper, pv, [fv], [type])	Loan payment amount	=PMT(5%/12,60,10000)
=FV(rate, nper, pmt, [pv], [type])	Future value of investment	=FV(5%/12,60,100,10000)
=PV(rate, nper, pmt, [fv], [type])	Present value of investment	=PV(5%/12,60,100,10000)
=NPV(rate, value1, [value2], ...)	Net present value	=NPV(5%,A1:A10)
=IRR(values, [guess])	Internal rate of return	=IRR(A1:A10)
=RATE(nper, pmt, pv, [fv], [type], [guess])	Interest rate per period	=RATE(60,-100,10000)

Advanced Excel Formulas

Formula	Description	Example
=ARRAYFORMULA(array_formula)	Applies formula to entire array (Google Sheets)	=ARRAYFORMULA(A1:A10*B1:B10)
=FILTER(range, condition1, [condition2], ...)	Filters range based on criteria	=FILTER(A1:B10,B1:B10>100)
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2], ...)	Sorts range (Excel 365)	=SORT(A1:B10,2,FALSE)
=UNIQUE(range)	Returns unique values (Excel 365)	=UNIQUE(A1:A100)
=SEQUENCE(rows, [columns], [start], [step])	Generates sequence (Excel 365)	=SEQUENCE(10,1,1,1)
=LET(name1, name_value1, calculation_or_name2, [name_value2, ...])	Assigns names to calculations (Excel 365)	=LET(x,A1+A2,y,B1+B2,x+y)
=LAMBDA([parameter1, parameter2, ...,] calculation)	Creates custom functions (Excel 365)	=LAMBDA(x,y,x+y)(A1,B1)

Excel Shortcuts Reference

Here are some essential Excel keyboard shortcuts:

• Ctrl+C - Copy
• Ctrl+V - Paste
• Ctrl+X - Cut
• Ctrl+Z - Undo
• Ctrl+Y - Redo
• Ctrl+S - Save
• Ctrl+F - Find
• Ctrl+H - Replace
• Ctrl+Arrow Key - Move to edge of data region
• Ctrl+Shift+Arrow Key - Select to edge of data region
• Alt+= - AutoSum
• F2 - Edit active cell
• F4 - Toggle absolute/relative references
• Ctrl+1 - Format cells dialog
• Ctrl+Shift+L - Toggle filters

Excel Tips

• Use F9 to evaluate parts of a formula when editing
• Press Ctrl+` (backtick) to toggle formula view
• Use Ctrl+Enter to enter same data in multiple cells
• Double-click the fill handle to auto-fill formulas down a column
• Use Alt+Enter to add line breaks within a cell
• Name ranges for easier formula writing and maintenance