How to calculate change cash in Excel

In this article, we will learn How to calculate change in cash in Excel.

Scenario:

We are going to learn how to calculate the number of notes given in change for the change amount. Sometimes working with excel we come across situation where calculating number of notes to change manually becomes long and tiring process which leads to errors and end up with mismatch in remaining amount data. Below is the explanation how to calculate change cash using the simple mathematical formulation and Excel functions.

How to solve the problem?

Here we will use the following excel functions to solve the problem:

  1. INT function
  2. SUMPRODUCT function

We will be requiring these above functions and some basic accounting knowledge. Change cash with the remaining amount in excel. Here we are given the remaining amount and section of available notes to denominate. 

Generic formula:

= INT ( ( amount - SUMPRODUCT ( previous_array )) / note_price )

amount : total amount to denominate

previous_array : array, to get the sum of the already considered amount.

note_price : considering note value for

Example :

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we have data where the amount to denominate or change is in column B and available notes are listed as an array along the 3rd row. Use the formula in C4 cell to get the 1st count of the highest note available.

Use the formula:

= INT ( ( $A4 - SUMPRODUCT ( $B$3:B$3,$B4:B4 ) ) / B$3 )

$(char) is used to freeze cells or arrays used in the formuola.

Explanation:

  1. B4 is the total amount to denominate.
  2. SUMPRODUCT($B$3:B$3,$B4:B4), extracts the amount which is already denominate.
  3. $A4-SUMPRODUCT($B$3:B$3,$B4:B4), extracts the remaining amount after the previous denominated amount.
  4. Then divide the amount with note value in B3 cell.
  5. INT function takes the integer part and then the other formula will work on the remaining amount.
  6. Integer part is the count of the highest note value.

Here the above image shows the count of the $500 note given in change of $113. Now we will copy this formula to the next available notes to get the total count. For the formula explained, I can copy this formula along a row by using the Ctrl + R.

Here we got the denominated note count for the $113 value. Which comes out to be one $100 note, one $10 note and three $1 notes.

113 = 100 + 10 + (3) * 1 

I can copy this formula along the column by using Ctrl + D as shown below.

Here we got the count of all the highest notes to be denominated for the 4 values in array. Similarly get the count for all notes as shown below.


As you can see we got the count of all values and for all the available notes. Here we can also remember to recheck the cashier's remaining amount. You can customize the formula for the required results.now get the count of the notes given for each transaction.

As you can see the formula works fine and very useful for any person who deals with cash notes.

Here are all the observational notes regarding using the formula.

Notes:

  1. The arrays in the formula only works with numbers.
  2. If the formula returns #VALUE error, check for the used braces must be present in an order in the formula as shown in the examples in the article.
  3. SUMPRODUCT function works on arrays. So make sure to check the array length, which must be equal or the formula returns error.
  4. Careful with the freezing array and cell references.
  5. Recheck the formula with partial breakdown values in formula using F9 shortcut for the selected part.

Hope this article about How to calculate change cash in Excel is explanatory. Find more articles on Summing or counting formulas here. If you liked our blogs, share it with your fristarts 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

Related Articles :

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

COUNTIFS with Dynamic Criteria Range : Count cells depstartent on other cell values in Excel.

COUNTIFS Two Criteria Match : Count cells matching two different criteria on list in excel.

COUNTIFS With OR For Multiple Criteria : Count cells having multiple criteria match using the OR function.

The COUNTIFS Function in Excel : Count cells dependent on other cell values.

How to Use Countif in VBA in Microsoft Excel : Count cells using Visual Basic for Applications code.

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles :

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

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

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 Youtube