Calculating the Average Difference between List Price and Sale Price in Microsoft Excel

In this article, we will learn how to calculate the average difference between list price and sales price. We can either use the “AVERAGE” function or we can use the “SUM” function along with “COUNT” function in Microsoft Excel.

SUM: This function is used to add the numbers in a range of cells.

Syntax: =SUM(number1,number2,……)

Let’s take an example to understand how we can add the numbers for rows.

We have sales data in range A1:A10. Now, we want to return the total value.

Follow below given steps:-

  • Select the cell A11
  • Write the formula =SUM(A2:A10)
  • Press Enter on your keyboard
  • The function will return the total value

 
img1
 
COUNT: This function is used to count the number of cells in a range that contains numbers.
 
img2
 
There could be times when you have a huge range of cells in a column or multiple columns, and you need to count the number of entries in all the cells put together. You can do this using one formula itself instead of using many count formulae together.

Count the number for Rows

Let’s consider the data in the range A2:A10.
 
img3
 
We need to count the number of values in this range.  In cell C2, put this formula –

=COUNT(A2:A10)
 
img4
 
We get the result as 9.

Count the number for Columns

Here, the data was present in a column. Similarly, if we have data in one row, like this –
 
img5
 
We can enter the formula in C4 as –

=COUNT(C2:K2)
 
img6
 
The result we get is 9 again.

Average: - This function is used for returning the average (arithmetic mean) of its arguments that can be numbers or names, arrays or reference that contains numbers.

 

The syntax of AVERAGE formula:-  =AVERAGE(number1,number2,……)

Let’s take an example to understand how the Average formula performs.

We have a list in the range A2:A11. Now, we want to see the average number in the list.

Follow below given steps:-

  • Select the cell B2, and write the formula in the cell
  • =AVERAGE(A2:A11)
  • Press Enter on the keyboard
  • The function will return the 34, which is the average of the range.

 
img7
 
Let’s take an example and understand how we can calculate average difference between the list price and sales price.

We have data in Range C4 to E13 in which column C contains the product, column D contains List price and column E contains sales price.
 
img8
 
To return the average difference between list price and sales price, follow the below given steps:-

  • Select the cell F4, and write the formula in the cell.
  • =AVERAGE(E4:E13-D4:D13)
  • Press Ctrl+Shift+Enter on the keyboard.
  • To calculate the average difference in amount, use the Average function in the Array formula.
  • The function will return the average difference between list price and sales price.

 
img9
 
We can also use the Sum function along with Count Function to return the same following the below mentioned steps:

  • Select the cell F4, and write the formula in the cell.
  • =(SUM(E4:E13)-SUM(D4:D13))/COUNT(D4:D13)
  • Press Enter on the keyboard.
  • The function will return the average difference between list price and sales price.

 
img10
 
These are the ways to calculate the average difference between list price and sales price in Microsoft Excel.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube