Menu
I'm trying to calculate a daily salary in a google spreadsheet.
in this particular job the hourly wage depends on the total amount of daily sales.
here are the specifics:
Sales - Hourly
$0.00 - $8.00
$350.00 - $9.00
$400.00 - $10.00
$500.00 - $11.00
$600.00 - $12.00
$700.00 - $14.00
$800.00 - $16.00
$900.00 - $18.00
$1,000.00 - $20.00
$1,300.00 - $23.00
$1,600.00 - $26.00
$2,000.00 - $30.00
for instance : if the sales rep sells $545 he will get $11 per hour untill he breaks the $600 mark.
in another sheet the employees input their check in and check out time, and their daily sales.
i have extracted the daily work hours and i would like to implement a switch statement equivalent that will calculate the hourly wage depending on the daily sales.
and then simply multiply them and calculate the daily salary.
thanks in advance.
448
The Google Sheets SWITCH function is a helpful tool for changing how a cell behaves based on the value in another cell. It's incredibly useful in data interpretation and presentation. To understand the SWITCH function, think of it as a function that can check multiple IF conditions.
On your computer, open a spreadsheet in Google Sheets. Find and replace. Next to "Find," type the word you want to find, If you want to replace the word, enter the new word next to "Replace with."
You want a VLOOKUP here. Off my mind, taking the sales column is A and hourly rate column is B and they have no headers (12 rows per value):
=VLOOKUP(400, $A$1:$B$12, 2, TRUE)
The 400 there would be the employee daily sales (you can change for a cell data).
More info about VLOOKUP here: https://support.google.com/docs/answer/3093318
PS: the first column needs to be sorted (that's the TRUE last argument) and be formatted as numeric (not as text), but I guess you already have that. If the list is not sorted, or is not numeric VLOOKUP only matches the exact search key
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
