How do I make one cell always equal to a specific number while still taking into account of other cells.

L

Legacy 463361

Guest
I need a formula which allows the 'Money Leftover' cell K7 to always equal 500. I've probably worded the above really vaguely, however I'll add an image to try and get explain myself.

Capture.PNG


What I need:

As I start to add in values under columns C-I, the values in K will either go up or down depending on if I spend money or earn it. If by the end of the month K7 is above £500, then I want the excess money to be transferred to L7. However, if K7 is below £500 then I want the value in H7 to adjust accordingly to get the value of K7 back to £500, which should result in an increase of H7 and the decrease in L7. The limit to which H7 + L7 can add up to is £1329.35 (B10), so when both of these cells are added together they cannot exceed B10.

Essentially, as H7 increases, L7 decreases, and vice versa, but K7 must always equal £500 while still taking into account the values in columns C-I. For example, money spent on 'Food' and 'Nights Out' will see a decrease in K7, meaning H7 must now increase as a result to bring the value of K7 back to £500.

I've very much complicated myself writing this out however I'm sure there is a way of doing it, I'm just not adept at Excel enough. I could just manually change the values at the end of each month, however it makes my life easier if I can simply put in values and the whole spreadsheet adjusts accordingly.

If you need any further information, please let me know.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
L

Legacy 463361

Guest
Typically it would be easier to adjust D7 rather than H7.

How so? D7 is simply for inputting miscellaneous spent money which doesn't fall under any of the other categories in columns C to I.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
Because most people find it easier to control their miscellaneous spending than they can control their income.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,635
Members
410,861
Latest member
Victor96
Top