Formatting Troubles with Spreadsheet tracking rent & utilities from roommates

[DumbEngineer]

Hi,


As the title suggests, I created a spreadsheet to keep track what bills my roommates owe. I have columns/cells that tell say what bills each individual owes. The trouble I am having comes in when trying to format cells so that they will tell me what bills people still owe after paying part of total amount. I can easily get the numerical value of what people still owe if they pay only part of the total amount, however, I want the spreadsheet to display the bill(s) they have not paid. For example, the expenses are rent, electricity, cable, and water. If a roommate has only paid the water bill but not the rent or other utilities, I want the column to say "Rent, Cable, and Electric still owed". Another example would be if a roommate only paid $100, I want the column to spit out that the roommate still owes utilities plus the amount of rent minus 100. And finally if the roommate has paid everything, I want "Paid in Full" displayed. I tried using a bunch of nested IF statements but soon realized there are well over 100 different combinations to have to go through. I was just wondering if there was a much simpler way of going about formatting the column to tell me what people still owe.


Thanks

 

[Gerald Higgins]

Hi, this will almost certainly be do-able, but it's difficult to give a precise answer because we have no idea what your data looks like.

Can you post a small sample of your data please (made up if you don't want to show real data) and say exactly what the results should be ?

Don't worry too much about explaining your household's finances - that's probably not too important here.
The main thing is to describe your data clearly, and what exactly you want to do with it.

 

[DumbEngineer]

Gerald,

Here is basically what my sheet looks like. The problem I'm having is in the Bills Owed Column and having the spreadsheet specify exactly what bills have yet to be paid. How should I format the column without using a bunch of nested IF statements? Or How should I make this spreadsheet more efficient?

Bills			Per Person
Rent	2200
Cable	127.85		21.308
Water	108.3		18.050
Electric House	273.72
Electric Garage	8.59
Electric Total	282.31		47.052
Grand total	2718.46		86.410
Roommates	Rent	Utilities	Amount Owed	Amount Paid	Amount Still Owed	Bill(s) Owed
Roommate A	350	86.410	436.41	100	336.41
Roommate B	330	86.410	416.41	416.41	0
Roommate C	400	86.410	486.41	21.31	465.1
Roommate D	420	86.410	506.41	18.05	488.36
Roommate E	370	86.410	456.41	47.05	409.36
Roommate F	330	86.410	416.41	65.10	351.31
Total	2200	518.460	2718.46

<tbody>
</tbody>