Forum Discussion
Solved
New to excel
Hi all,
I have absolutely no experience with Excel and could do with some help. Please see the screen shot attached below.
I apologise if I don't explain it well but I am hoping you will understand what I wish to do. The figures shown are just for the purposes of help but I am looking to set a document for myself and my partner to keep an eye on the budget we have for our upcoming wedding.
I would like to be able to do the following:
Column C is the available budget and I would like any costs entered in column B to be automatically deducted from this and the remainder automatically entered into the cell below. I know this can be done by selecting C4 and typing the formula =C3-B3 but I would have to do this for each row.
The next thing is for Column D (money paid) to subtract from column B (cost) and enter the remaining amount into column E (left to pay).
Finally the money received would be what family have offered to help with so as this is received i would want this to total at the bottom so we can see how much of the agreed amount we have received to keep track for those members.
I basically want the amounts to change automatically as i enter the data without having to type each formula in each cell/row.
I hope I have explained this correctly but happy to give a better explanation ( if I can).
Thank you in advance
I kept the basic arrangement you proposed but made 1 change.
I believe it's best to list the starting budget at the top of sheet and have the rows display the 'money on hand' to take into account the money spent in the same row. In other words, the 'amount' in the first line won't be 10,000 - it will be 3,005.
Next, I created dynamic named ranges for AmountReceived, Cost, LeftToPay, and MoneyPaid (I've included some notes on these in the attached workbook).
Dynamic named ranges make the formula in the 'LeftToPay' column very easy, and you won't have to update anything:
=Cost-MoneyPaidThe amount is then determined with some basic accumulation:
=LET( accumulate, LAMBDA(a, v, a + v), RunningCost, SCAN(0, Cost, accumulate), Budget - RunningCost )
I HAVE CONVERTED IT INTO two tables, first table according as you desired
in second table i have added column family members.as you enter data, bottom total sum will change accordingly, which will help you.
I kept the basic arrangement you proposed but made 1 change.
I believe it's best to list the starting budget at the top of sheet and have the rows display the 'money on hand' to take into account the money spent in the same row. In other words, the 'amount' in the first line won't be 10,000 - it will be 3,005.
Next, I created dynamic named ranges for AmountReceived, Cost, LeftToPay, and MoneyPaid (I've included some notes on these in the attached workbook).
Dynamic named ranges make the formula in the 'LeftToPay' column very easy, and you won't have to update anything:
=Cost-MoneyPaidThe amount is then determined with some basic accumulation:
=LET( accumulate, LAMBDA(a, v, a + v), RunningCost, SCAN(0, Cost, accumulate), Budget - RunningCost )- Thank you so much for taking the time to do this. This is exactly what I wanted and looking at the formulas I would never have been able to do this.
Thanks again 🙂- You're welcome!
