Checkbox that changes spreadsheet formula?

L4mbov

New Member
Joined
Oct 30, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hey guys, here's my little riddle that I'm trying to solve..

A restaurant gets "x amount" of tips for the night.
The TOTAL of "tips for the night" gets split 70 / 30. (Waiters / Kitchen)
The staff working as the "Waiter" role get 70% of the total.
The staff working as the "Kitchen" get 30% of the total.
------
So far, I have this worked out this in my spreadsheet (refer to upload)
Firstly, the formula of splitting the amount of "TOTAL tips for the night" between Waiters and Kitchen is straight-forward.
Secondly, the formula of finding out how much each of the staff members from the Waiters vs Kitchen is also fine.

- Total Tips of Waiters (=C8*0.7)
- Total Tips for each Waiter staff member (=C10/D7)

- Total Tips for Kitchen (C8*0.3)
- Total Tips for each Kitchen staff member (=C14/G7)
(Refer to screenshot)
// However, there are shifts where the owner of the restaurant also works as a waiter, alongside the waiters.
She's selflessly decided to give away all of her tips and split them 50/50 between the waiters and kitchen. \\

Aim of the excel spreadsheet (Here's the tricky part)

I want the checkbox that indicates whether or not the owner of the restaurant is working that night (so far turns cell green to show that's checked, refer to photo)
to automatically adjust the Totals for both categories and their sums (Waiters + individual member / Kitchen + individual member)
WHEN we know that the owner's tips get allocated 50/50 between the Waiters and Kitchen.
When the box is ticked (it means, she's working --> totals get updated and include her 50/50 tips share), when the box is unchecked it just splits it, as explained 70/30 rule.

Thank you kindly!
Martin.
 

Attachments

  • EXCEL_lJnHGHn49y.png
    EXCEL_lJnHGHn49y.png
    58.1 KB · Views: 6

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
190
Office Version
  1. 365
Platform
  1. Windows
By right clicking on the checkbox, you should be able to set a spreadsheet cell that changes when the box is ticked (or not).
Your other formulae can adjust themselves based on the value of this cell
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,611
Messages
5,765,414
Members
425,285
Latest member
andypandypoo

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
Top