Excel Notes – With Formulas and Functions

MICROSOFT EXCEL 2016

Excel Notes – With Formulas and Functions:-

MS-Excel 2016 भी Ms-office का भाग है। MS-Excel की file को spread sheet or electronic sheet कहते है। MS-Excel की file का Extension name .xlsx होता है। MS-Excel की one workbook में by default 1 sheets होती है। जिन्हें बढाकर 255 sheets तक किया जा  सकता है। MS-Excel 2016 की एक sheet में 16384 columns and 1048576 rows होते है। sheet के जिस cell में pointer present होता है उसे active cell कहते है। MS-Excel 2016 में निम्न  लिखित  functions का use किया जाता  है।

1. Date and Time functions
2. Engineering functions
3. Math and Trigonometry functions
4. Statistical functions
5. Text functions
6. Logical functions
7. Lookup and reference functions

Read My Tally Notes

1. Date and Time functions
For current date
= today ()
For current date & time
= now ()
For date value
= datevalue(“DD/MM/YYYY”)-datavalue(“DD/MM/YYYY”)
For date
= date(YYYY,MM,DD)
For day
= day(cell reference)
For month
= month(cell reference)
For year
= year(cell reference)
For time
= time(HH,MM,SS)
For hour
= hour(cell reference)
For minute
= minute(cell reference)
For second
= second(cell reference)
For weekday
= weekday(cell reference)
2. Engineering functions
Conversion from decimal to binary
= DEC2BIN (cell reference)
Conversion form decimal to hexadecimal
= DEC2HEX (cell reference)
Conversion from decimal to octal
= DEC2OCT (cell reference)
Conversion from binary to decimal
= BIN2DEC (cell reference)
Conversion from binary to hexadecimal
= BIN2HEX (cell reference)
Conversion from binary to octal
= BIN2OCT (cell reference)
Conversion from octal to binary
= OCT2BIN (cell reference)
Conversion from octal to decimal
= OCT2DEC (cell reference)
Conversion from octal to hexadecimal
= OCT2HEX (cell reference)
Conversion from hexadecimal to binary
= HEX2BIN (cell reference)
Conversion from hexadecimal to decimal
= HEX2DEC (cell reference)
Conversion from hexadecimal to octal
= HEX2OCT (cell reference)
For Power
= impower(number, power)
3. Math and Trigonometry functions
For modulus
= mod (cell reference, cell reference)
For Power
= power(number, power)
For Multiplication
= product (cell reference, cell reference)
For Multiplication more than two values
= product (first cell reference: last cell reference)
For divide
= (cell reference / cell reference)
For Round digit
=round(number,num_digits)
For Sqrt
= sqrt(cell reference)
For addition two value
= sum (cell reference, cell reference)
For addition more than two values
= sum (first cell reference: last cell reference)
For Subtraction
= (cell reference – cell reference)
For Sumif
=sumif(first_cell reference:last_cell reference ,”range”)

Excel Notes – With Formulas and Functions

4. Statistical functions
For Average
= average (first cell reference: last cell reference)
For Averageif
=averageif(first_cell reference : last_cell reference,”range”)
For Counting values
= count (first cell reference: last cell reference)
For Countif
=countif(first_cell reference:last_cell reference,”range”)
For find out maximum number
= max (first cell reference: last cell reference)
For find out minimum number
= min (first cell reference: last cell reference)
5. Text functions
For add more than two words
=concatenate(first_cellreference , second_cellreference)
For change Upper case to Lower case
=lower(cell reference)
For change Lower case to Upper case
=upper(cell reference)
6. Logical functions
If condition
=if(logical_test,value_if_true,value_if_false)