Excel Trivia | Everything about Excel Function and Formulas: MATHEMATICS

Showing posts with label MATHEMATICS. Show all posts

How to Use SUMIF Function | Microsoft Excel

Urvil Darji August 09, 2019 0

Sumif Function is used when calculate sum of the specific a limited criteria of the data. For Example we have a data from 1 to 10 and we want sum of the number which are less than five then sumif ignores the other numbers which are greater than 5.

Syntax of SUMIF Function

=SUMIF(range,criteria,[sum_range])

Arguments of SUMIF Function

range: required. a range in which you want to perform the operation. range should contain numbers other than value will not consider to sum.
criteria: required. criteria is a logical operation. as per the criteria is satisfied in the range sumif will returns a result. Criteria should be number, text, reference, expression. Text or special characters should be in double quote("text")
sum_range: optional. if you wnat another range from the range you specified to satisfy the criteria.

Wild Card Characters:

? for matching any single character
* for matching any sequence of character
~ to find actual wild card character(? or *) us tild(~) preceding the character

Behaviour of SUMIF Function

For Numerical

=SUMIF(A1:A8,"<20") will sum the numbers which are less than 20 in the column A1:A8

For Text

=SUMIF(A1:A8,"Jaguar",B1:B8) will sum the number in range of the A1:A8 contains Jaguar

Use of SUMIF Function

To calculate the number as per the criteria defined

Return of the SUMIF Function

The sum of the numbers

Tags # MATHEMATICS Continue Reading

How to Use AVERAGE Function | Microsoft Excel

Urvil Darji June 26, 2018 0

Average function is used to find the mean value of given data. It can be count up to the 255 arguments. It can calculate numbers, cell references, arrays.

Syntax For AVERAGE Function

=AVERAGE(Number1, Number2,....)

Where the Number1 is the first number of the given data. Number1 is must required for function
And Number2 is optional it could be leading up to cell no 255, it is a reference cell no and range also.

If you want to know the average of the data, it can be seen at the status bar of the excel sheet. You just have to select the data in excel sheet and the average of data will appear in a status bar.

When using the function AVERAGE With range =Average(A1: A10) will return the mean value of cell no A1 to A10.

When using the average function for manual data like 10,20,30 then the function will return the mean value of the numbers. =AVERAGE(10,20,30).

The average function has also some limitations.

The average function can count numbers, names, reference cell, arrays and constant.

The average function automatically counts 0 if there are no values in the cell.

You can use this function vertically and horizontally also

Sources:exceljet

Tags # MATHEMATICS Continue Reading

How to Use SUM Function | Microsoft Excel

Urvil Darji June 20, 2018 0

The sum function is very useful in excel. It will give you the sum of the 1^st cell to 255^th cell.

Lets we see basic Sum Function

The sum is to add values. You can add two values and more than two values. As per the syntax you use.

=SUM (A1: A10)

=SUM (number1: number N) Where N can be 2 to 255

SUM Function =SUM(A1: A10)

This syntax will count the sum of the values contains in cell no A1 to A10. It counts only numerical values if you write a non-numerical then the system will count non-numerical as 0. It can sum up to the 1^st cell to 255^th cell.

=SUM (A1:A5,C6:C10)

=SUM (number N: number N, number N: number N) Where N can be 1 to 255

This syntax will count the values of your selected cells. It is the flexible formula.

You just have to select first range and place comma and select the second range. That will give you the sum of both the selected ranges of your sheet.

SUM at Status bar

If you select the data than status bar shows you SUM, AVERAGE and COUNTS of your data. It is one of the quickest ways to see SUM.

Sum Function Using Status Bar

By using right click on the status bar will show other option. What you want the quick result of. Available options of status bar are SUM, AVERAGE, COUNT, NUMERICAL COUNT, MINIMUM, and MAXIMUM.

Flexible addition Using SUM

The flexible sum is used when you want to add different ranges of cells and neglect the specific or avoid the cells which you do no\t want to count in your sum.

Sum Function Flexibility

Different ranges of the formula will highlight with different colors. It can calculate Row range with column range also.

Using AutoSum

First of all you have to write your data and select the next cell from the last entry

Now Select Autosum option from the ribbon, Which is located at right upper corner of the workbook

or Use shortcut Key"Alt+="

Press enter

Error Faced in SUM Function

1. The sum value shows #### in place of the result
It means the return value is longer than the cell width. It is solved by expanding cell width

2. In the result cell shows function but not the result value
if the values in the cell are formatted as text than this type of error comes. if you convert values to number from text this error will omit.

3. Only numerical values are counted in sum. If non-numerical values are placed in the sum range system will count it as a 0.

4. #NAME
This is a syntax error. If you have placed or by mistake an extra character existed in formula than this type of error occur. For Ex =SUM(A1:\A10)