Forum Discussion

krissy_555's avatar
krissy_555
Copper Contributor
Dec 14, 2023

Excel: Help, PLEASE!?!?

So  is there a way to have excel examine data on a sheet, find the pattern and show you a potential formula you can use on data in future sheets.    I am attempting to show a running balance.   I...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Dec 15, 2023

krissy_555 

It sounds like you want to create a running balance in Excel based on a starting balance, deposits, and withdrawals. You can use the SUM function in combination with OFFSET or INDEX-MATCH to dynamically calculate the running balance. Here is a step-by-step guide:

Assuming your data is in columns A, B, and C, where:

  • Column A: Loads (Deposits)
  • Column B: Withdrawals
  • Column C: Current Balance

And you have a starting balance at the top in cell C1.

Step 1: Enter Starting Balance

In cell C2 (assuming C1 has the starting balance), enter the following formula:

=C1 + A2 - B2

Step 2: Copy the Formula

Copy the formula in cell C2 down for all rows where you have data in either the Loads or Withdrawals column.

Alternative Step Using INDEX-MATCH

If your data has headers, you can use INDEX-MATCH to dynamically find the last non-empty cell in the Current Balance column. Assuming your headers are in row 1:

Step 1: Enter Starting Balance

In cell C2, enter the starting balance.

Step 2: Enter the Formula

In cell C3, enter the following formula:

=INDEX(C:C,MATCH(2,1/(A:A<>""),1))-B3+A3

Step 3: Copy the Formula

Copy the formula in cell C3 down for all rows where you have data in either the Loads or Withdrawals column.

This formula finds the last non-empty cell in column A (Loads) using MATCH and then uses INDEX to get the corresponding value in the Current Balance column. It then adds the current deposit and subtracts the current withdrawal.

This way, you only need to enter the starting balance, and the formula will automatically calculate the running balance based on deposits and withdrawals.

Remember to adjust the column references based on the actual location of your data in your spreadsheet. If these steps didn't help you, I recommend you add more information, attached a link with the possible information that may be needed. The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Resources