BRIEF OF EXCEL FORMULAS (PART-1)

EBEES INFOTRAIN CONSULTANCY SERVICES PVT. LTD.

1. SUM : 

Summary 

The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.

Purpose 

Add numbers together

Return value

The sum of values supplied.

Syntax 

=SUM (number1, [number2], [number3], ...)

Examples

In the example shown, the formula in D12 is:

=SUM(D6:D10) // returns 9.05

2.Round() : 

Summary 

The Excel ROUND function returns a number rounded to a given number of digits. The ROUND function can round to the right or left of the decimal point.





Purpose 


Round a number to a given number of digits




Return value 


A rounded number.




Syntax 



=ROUND (number, num_digits)





Arguments 







number - The number to round.
num_digits - The number of digits to which number should be rounded

3.Round down() : 

Summary 



The Excel ROUNDDOWN function returns a number rounded down to a given number of decimal places. Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDDOWN rounds all numbers 1-9 down.





Purpose 


Round a number down to a given number of digits




Return value 


A rounded number.




Syntax 



=ROUNDDOWN (number, num_digits)





Arguments 







number - The number to round down.
num_digits - The number of digits to which number should be rounded down.

4.Round Up() : 

Generic formula 



=ROUNDUP(number,digits)






Explanation 



If you need to round a number up, regardless of the value of the digits being rounded (i.e. you want to force any number to round up, regardless of value) you can use the ROUNDUP function with a specified number of digits.

In the example, the formula in cell D7 is

=ROUNDUP(B7,C7)



5.ABS() : 









Summary 



The Excel ABS function returns the absolute value of a number. Negative numbers are converted to positive numbers, and positive numbers are unaffected.









Purpose 


Find the absolute value of a number








Return value 


A positive number.






Syntax 



=ABS (number)






Arguments 







number - The number to get the absolute value of.



6. SQRT() : 





























Summary 



The Excel SQRT function returns the square root of a positive number. SQRT returns an error if number is negative.





Purpose 


Find the positive square root of a number




Return value 


Positive square root




Syntax 



=SQRT (number)





Arguments 







number - The number to get the square root of.

7. Trunc () : 

Summary 

The Excel TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.





Purpose 


Truncate a number to a given precision




Return value 


A truncated number




Syntax 



=TRUNC (number, [num_digits])





Arguments 







number - The number to truncate.
num_digits - [optional] The precision of the truncation (default is 0).

8. Sumif  () : 



Generic formula 




=SUMIFS(sum_rng, crit_rng1, crit1, crit_rng2, crit2)






Explanation 



If you need to sum numbers based on multiple criteria, you can use the SUMIFS function.

In the example shown, the formula in G6 is:

=SUMIFS(D4:D11,B4:B11,"blue",C4:C11,">15")



How this formula works

The first range (D4:D11) are the cells to sum, called the "sum range".

Criteria are supplied in pairs...(range / criteria).

The first criteria pair is (B4:B11 / "blue"). This means cells in B4:B11 must equal "blue".

The second criteria pair is (C4:C11 / ">15"). Cells in C4:C11 must *also* be > 15.

These cells in the sum range will only be summed when all criteria pass.




9.ODD () : 






Summary 


The Excel ODD function returns the next odd integer after rounding a given number up. The ODD function always rounds numbers up (away from zero) so positive numbers become larger and negative numbers become smaller (i.e. more negative).




Purpose 


Round a number up to the next odd integer




Return value 


A rounded number.




Syntax 



=ODD (number)





Arguments 







number - The number to round up to an odd integer










9.POWER () : 
















Summary 



The Excel POWER function returns a number to a given power. The POWER function works like an exponent in a standard math equation.







Purpose 


Raise a number to a power





Return value 


Number raised to power




Syntax 



=POWER (number, power)





Arguments 







number - Number to raise to a power.
power - Exponent to raise power to.










Usage notes 



The POWER function can be used to raise a number to a given power. The POWER function works  like an exponent in a standard math equation. In Excel, exponentiation is handled with the caret (^) operator, so:

2^2=POWER(2,2)=4
2^3=POWER(2,3)=8
2^4=POWER(2,4)=16







TO Be Continueeeeee............................(Next Blog)