OFFSET Function is used to move or skip either row or column. Suppose you want to know what data available after every 2 rows or column the offset function is the best choice to get perfect result. It also used with other fucntion available in excel to make it more powerful. For example =SUM(OFFSET(A1,2,2,2,2))
SUBTOTAL is a great function available in excel. It only look the visible data and ignore the hidden data. So when you want to apply a Count function for only visible data only, when you are finding the data by applying filter or you have some hidden rows. The Count function count the number of numeric data available in the selected range. Use the formula at the end of your database where you want to apply this both function as shown in image and video example..
There is a Simple way of finding the percentage. Multiply the obtain marks , score or anything you have scored out of the total scoreboard then multiply the value into 100 and divide the sum of total scoreboard value and as a result the value found will be your percentage. For example there are 4 subject of 100 marks each so sum of total marks = 400 and assume that 1 student got 60 marks each in every 4 subject so in total he obtain 240 marks from sum of total 400 marks. So as a result the Formula will be Obtain marks * 100 / Total Marks. = 240 *100 / 400 = 60%
According to math for finding percentage is to divide the numerator by the denominator then multiply the output with 100. Suppose you want to find the percentage of 50/100 then divide 50 with 100 the answer will be 1/2 the multi...
Excel SUBTOTAL function returns the subtotal of the numeric values in a column from a database. Excel SUBTOTAL function is used as part of a formula in a cell of a worksheet for column or row according to your database. Subtotal has several different types of option available with unique code which you can use it with some of them are SUM, COUNT, COUNTA and many more for auto calculate.
SUMIFS function adds all numeric values in a given range of cells based on more then one criteria. In this function you have to give first sum range ( from which we need to get the total ) then criteria range ( Name column range ) and then criteria1 and then criteria range 2 then criteria 2 (36). Example given in image below.
PARAMETER: = SUMIFS ( Sum Range, Criteria Range, Criteria 1, Criteria Range 2, Criteria 2 )
IMPLEMENT: Here the auditor want to know that in this year how many car sold for Maruti Swift and if sold he want a total of the price sold. So the sumifs will take Sum range column (C) and range1 column (A) and 2 criteria 1st (Column D) Criteria range 2 Column (B) and criteria2 Column (E)
FORMULA: =SUMIFS(C2:C11,A2:A11,D2,B2:B11,E2)
...
SUMIF function adds all numeric numbers in a given range of cells based on one criteria. Suppose there is name in column A and amount in column B and in column A if a name is repeating more than 1 time and you want to add all the amount It have next to column then Sumif is a best formula to make your work easy. Example as given below.
PARAMETER: =SUMIF ( RANGE, CRITERIA, SUMRANGE )
IMPLEMENT: First select the range column, then select or add a criteria, then select the sum range and its done. Example are provided in image and video.
FORMULA : =SUMIF(A2:A9,C2,B2:B9)
SUM function is used to get automatic total of the numbers value in a selected range. Thereafter If you change the value in any of the selected range it will auto calculate and give you the exact final result. Please note that If there are text in some cell and some cell are with numeric value then sum function will ignore the text cells and will give you the result of numeric cells. It makes work very much easy when you have a large database. Example shown in image below.
PARAMETER : = SUM ( RANGE )
FORMULA : =SUM(B2:F5)
