Hey forum
I have always been able to get solutions to my excel quandarys here so after spending a few hours trying to puzzle this out I'm here to ask the experts. I have created a reservation form for a monthly brunch that we host. Every guest is allowed 2 complimentary guests with a paid fee for additional guests. Up until now I have had the people taking the reservations manually enter the comps and the paid but I know there has to be an easier way to calculate all of it if we just enter the total number of additional guests in the party.
I can copy and past the formulas etc so I am only focused on the first block columns A-H
I want my front line team to enter the total number of additional guests that will be attending in Column C so cell C3, I can't quite figure out how to make D3 only equal a max of 2 and then E3 be the balance remaining. So if C3 is 17 guests then D3 should only equal 2 for the allowed comps and E3 would equal the balance 15 that would be paid. I already have what I need in F3 and G3. I can't get the right combination of IF>=formulas to get the math to work out right.
I hope that it goes without saying if the count is 2 or less then D3 would be =C3 and E3 would be 0
I will be hiding most of the columns so my team will no longer see the breakdowns just the total of guests, total in party and amount due.
I have been working in the vein of IF($c3>=2,D3,2,C3,IF($c3>=2,F3(c$c3-2)) but it's not right and I'm just stumped.
I am open to suggestions and will take any help I can get.
Thanks for taking a look!
Bookmarks