Forum Discussion
Columns being done?
I'm new to Excel and the boss wanted this to be done:
Take five columns row by row
The first column contains a number of a check. This is what makes this row unique he says.
The next column has a name in English text in each row.
The third column has a value in dollars and this is a deposit.
The fourth column has a value in dollars and this is a withdrawal.
The fifth column is to be computed this way:
If this is a deposit then add this to the value that was in the fifth column and put that into the current row in the fifth column.
If this is a withdrawal then subtract this value from what was in the fifth column
and store it into the current row in the fifth column.
I'm stumped as to how this might be done and need help !!!!
Hi,
You said: "If this is a deposit then add this to the value that was in the fifth column and put that into the current row in the fifth column.
If this is a withdrawal then subtract this value from what was in the fifth column
and store it into the current row in the fifth column."
You cannot do all that stuff in one column (column 5)
You need a sixth column to put the formula in it!
Please provide us with a sample of your data.
Haytham:
Here's a sample of the data that the boss gave me. It's not in a sheet, but it shows you what he wanted done.
Check number Name Deposit Withdrawal Balance
000001 John Revere 500.00 500.00
000002 Paul Johnson 395.67
000003 Verizon 200.00
The first check was to be treated as a starting balance, so no problem there.
The rest of the task had me stumped,. If it takes more than five columns, the boss
would say okay, just get the job done. I'm new to Excel and really don't have much
experience with it . HELP!!
Sorry had a hiccup in the formula if you saw previous post.Here is the proper formula. You would need to put this formula in the 3rd row - 5th column. Since the 2nd row is a set amount , either negative or positive, the rest will follow using the formula. Just remember to mark the balance as a negative in the 2nd row if it starts with a Withdraw.
=IF(C3>0, C3+E2, E2-D3)
Check Number Name Deposit Withdraw Balance 1 John 500 500 2 Paul 395.67 895.67 3 Verison 200 695.67 You could also add this to the 2nd row 5th column to start it but use the other formula to do the other calculations.
=IF(C2>0, C2,0-D2)
Daniel's results are wrong because you don't want to give John, Paul, and Verison a shared balance.
What is needed is two tables. One that stores the transactions and one that stores the balance for each customer.
I used a SumIf to calculate the sum of transactions and deposits and then subtracted the withdrawals from deposits. My formula
=SUMIF(B3:B5, F3, C3:C5)-SUMIF(B3:B5, F3, D3:D5)
Looks at the Names in B3:B5 and compares them to the name in F3. If they match, the corresponding values in C3:C5 or D3:D5 are added respectively.
I have a sample solution in the attached file.
There are some experts lurking around that'll provide a better answer than me, but this will solve your problem if you update the ranges and cells to match your problem.
Peter:
Looking at the formula:
Correct me if I'm wrong as to how I understand your answer.
=SUMIF(value in column B starting at row 3:value in column B ending at last row in column B,name in F3,value in column C starting at row 3:value in column 3 ending at last row in column C)-SUMIF(value in column B starting at row 3:value in column B ending at last row in column B,name in F3,value in column D starting at row 3:value in column D ending at last row in column D)
Are you sure that this will change the balances correctly? I think that F3 may need to change from time to time but am not sure, since you store each transaction in a separate row and it has to synch with each separate name. Help?
Without over thinking it and using IF formulas, the simplest answer is just basic + and - mathematics.
The first balance at E2 is simply the Deposit - Withdrawal
Then each subsequent balance is the Balance (row above) + Deposit (this row) - Withdrawal (this row). Copy the second formula down to the end of the data.
A B C D E 1 Check Number Name Deposit Withdrawal Balance Column E Formula 2 00001 John Revere 500.00 500.00 =C2-D2 3 00002 Paul Johnson 395.67 895.67 =E2+C3-D3 4 00003 Verizon 200.00 695.67 =E3+C4-D4
