DumbEngineer
New Member
- Joined
- Nov 1, 2017
- Messages
- 5
I am trying to figure out which elements of a set of numbers (amounts) total to a target value. I am creating a google sheet to keep track of what my roommates pay me, where I receive both full and partial payments of bills from my roommates but documentation indicating what bills are being paid is lacking. The spreadsheet below is similar to what I am doing. In the Bill Owed column, I want to format the cells so that it tells me which bills still need to be paid. For example, for Roommate A the Bills Owed Column should display "Cable, Water, Electric, & $250". For Roommate B it should say "None". For Roommate C is should say "Water, Electric, Rent". For Roommate D is should say "Cable, Water, Rent". Roommate F would still owe "Water, Rent". I think I might be able to use the solver command; however, I'm not quite sure how to use it. I also think it might run into a problem with Roommate A where they only paid $100 and not any bill in particular. How do I fix this and is there a way this spreadsheet could be more efficient? I don't want to use a bunch of nested IF statements.
Thanks
[TABLE="width: 500"]
<tbody>[TR]
[TD]Bills[/TD]
[TD][/TD]
[TD][/TD]
[TD]Per Person[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rent[/TD]
[TD]2200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cable[/TD]
[TD]127.85[/TD]
[TD][/TD]
[TD]21.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]108.30[/TD]
[TD][/TD]
[TD]18.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electric House[/TD]
[TD]273.72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electric Garage[/TD]
[TD]8.59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electric Total[/TD]
[TD]282.31[/TD]
[TD][/TD]
[TD]47.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]2718.46[/TD]
[TD][/TD]
[TD]86.41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommates[/TD]
[TD]Rent[/TD]
[TD]Utilities[/TD]
[TD]Amount Owed[/TD]
[TD]Amount Paid[/TD]
[TD]Amount Still Owed[/TD]
[TD]Bill Owed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate A[/TD]
[TD]350[/TD]
[TD]86.41[/TD]
[TD]436.41[/TD]
[TD]100[/TD]
[TD]336.41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate B[/TD]
[TD]330[/TD]
[TD]86.41[/TD]
[TD]416.41[/TD]
[TD]416.41[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate C[/TD]
[TD]400[/TD]
[TD]86.41[/TD]
[TD]486.41[/TD]
[TD]21.31[/TD]
[TD]465.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate D[/TD]
[TD]420[/TD]
[TD]86.41[/TD]
[TD]506.41[/TD]
[TD]47.05[/TD]
[TD]459.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate E[/TD]
[TD]370[/TD]
[TD]86.41[/TD]
[TD]456.41[/TD]
[TD]21.31[/TD]
[TD]435.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate F[/TD]
[TD]330[/TD]
[TD]86.41[/TD]
[TD]416.41[/TD]
[TD]68.36[/TD]
[TD]348.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]2200[/TD]
[TD]518.46[/TD]
[TD]2718.46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks
[TABLE="width: 500"]
<tbody>[TR]
[TD]Bills[/TD]
[TD][/TD]
[TD][/TD]
[TD]Per Person[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rent[/TD]
[TD]2200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cable[/TD]
[TD]127.85[/TD]
[TD][/TD]
[TD]21.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]108.30[/TD]
[TD][/TD]
[TD]18.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electric House[/TD]
[TD]273.72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electric Garage[/TD]
[TD]8.59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electric Total[/TD]
[TD]282.31[/TD]
[TD][/TD]
[TD]47.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]2718.46[/TD]
[TD][/TD]
[TD]86.41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommates[/TD]
[TD]Rent[/TD]
[TD]Utilities[/TD]
[TD]Amount Owed[/TD]
[TD]Amount Paid[/TD]
[TD]Amount Still Owed[/TD]
[TD]Bill Owed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate A[/TD]
[TD]350[/TD]
[TD]86.41[/TD]
[TD]436.41[/TD]
[TD]100[/TD]
[TD]336.41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate B[/TD]
[TD]330[/TD]
[TD]86.41[/TD]
[TD]416.41[/TD]
[TD]416.41[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate C[/TD]
[TD]400[/TD]
[TD]86.41[/TD]
[TD]486.41[/TD]
[TD]21.31[/TD]
[TD]465.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate D[/TD]
[TD]420[/TD]
[TD]86.41[/TD]
[TD]506.41[/TD]
[TD]47.05[/TD]
[TD]459.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate E[/TD]
[TD]370[/TD]
[TD]86.41[/TD]
[TD]456.41[/TD]
[TD]21.31[/TD]
[TD]435.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roommate F[/TD]
[TD]330[/TD]
[TD]86.41[/TD]
[TD]416.41[/TD]
[TD]68.36[/TD]
[TD]348.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]2200[/TD]
[TD]518.46[/TD]
[TD]2718.46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]