Excel/Google Sheets Solver Difficulty

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]

 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top