NazJam Tech Blog

Today while thinking of something useful to write I came across an idea of a post regarding some Basic Excel formulas to aid Microsoft Excel beginners in their everyday arithmetic. So, here it is for anyone starting in Excel to excel.

In the article below wherever I’ll write initial cell location and final cell location, I don’t mean you to write them exactly, you’ll have to write the cell location (ex. A1, B1, C1…) from where the range starts, in place of initial cell location and cell location (ex. A10, B10, C10…) at where the range ends in place of final cell location.

Adding values:

Click on an empty cell location and type =SUM(initial cell location:final cell location). For example, in the figure below I have selected cell values of 1 to 10 ranging from the initial cell location A1 to the final cell location A10. The formula simply adds all these values of the selected range, which totals 55. My formula to calculate the sum is: =SUM(A1:A10). Inside the brackets, you have to enter initial and final cell locations of the range you want to sum up.

To sum up only two values anywhere from the range I simply type for my example: =A6+A9, getting the output 15.

Obtaining Minimum and Maximum from a range of values:

Given a range of different values in the cells in MS Excel, you are able to find the minimum value out. Simply select an empty cell and type =MIN(initial cell location:final cell location) there. For example, I’ve selected the range A1 to A10. The formula =MIN(A1:A10), finding the minimum value of the range, outputs 1.

For maximum, min is just replaced with max as: =MAX(initial cell location:final cell location). The maximum value of my range A1 to A10 which is 10, is the output of =MAX(A1:A10). Its usefulness lies in finding minimums and maximums over long ranges of values.

Finding Average:

As you’ve seen, MS Excel formulas I’ve written are quiet easy to remember as they spell no differently with what they do. So are the average formulas: one for a range: =AVERAGE(initial cell location:final cell location), and one for averaging values of two cell locations. Same as I’ve shown above, I’ve selected the range A1 to A10, averaging which gives 5.5.

When two values are to be averaged we’ll have the colon(:) replaced with coma(,) between two cell locations as: =AVERAGE(A2,A4), which gives the output 3.

Counting number of values in a range:

Finding the total number of entries in a long range of values is as simple as: =COUNT(initial cell location:final cell location). It counts the total number of range cells that have values entered in them. For example, I’ve counted number of entries between A1 and A10. For this I’ve typed: =COUNT(A1:A10), which gives 9 (and not 10) as I have an empty A7 cell location.

Finding the Percentage:

It is also same as the formula that we use to calculate percentage manually, where we divide the min. value by the max. value and multiply the divided value by 100 as, =min.value/max.value*100. For my example: A3/A10*100, gives 30. Our output here is 30%.

Multiply, Subtract and Divide values:

Formulas for these operations are not at all different from ordinary arithmetic, thus, going directly to examples, where I am Multiplying two values by just typing =A9*A5, Subtracting A4 from A10 by =A10-A4, and Dividing A8 by A5 as =A8/A5, give me the outputs 45, 6 and 1.6 respectively.