Forum Discussion
Creating an automatic fee calculator based on formulas
Hello everyone.
I have a sort of a challenge for the Excel Experts, I think.
At my job people can work on their day off and depending on the requesting entity and depending on the time of day, they get paid different amounts.
So basically, there are 2 tables (1 and 2) for differenciating requesting entities and the time of day, which is divided in day time and night time.
Example:
Table 1 Day time Fee - 20€/Night time Fee - 35€
Table 2 Day time Fee - 15€/Night time Fee - 30€
What I want help with is creating an excel spreadsheet that would automatically calculate a fee, according to the cell colors so if it's blue it's always 20€ and if it's red it's always 35€ (not the real values) since there will be two spreadsheets for each table rotation, but both have to be in sequence.
Meaning, worker 2 will only be cast after worker 1 has been casted or if worker 1 is unavaiable. This means I will put the date in the cell so I don't cast the same worker for the same day and there will be times where some workers are cast several times before another one is due to unavailability.
When I put the date, I'm creating a sequence and I can see which workers have had more services than the other one, so I will start casting the one with the lesser amount first.
This rotation is done every 3 months, so there might be times where one worker during the first month will work 11 times and during the second month he will only work 4 times, which would mean it's balanced.
I think it's sort of clear of what I need for my job, but if you have any doubts, please do contact me. Thanks in advance!
You are describing a situation with multiple moving parts here. It would be good to break them apart and deal with them in sequence rather than trying verbally to describe the whole mess.
Let me suggest you start with the varying pay tables, Tables 1 and 2, day and night, and make that into a single table. And don't use color to differentiate; use a code of some kind. The color can be derived from the code, but Excel is far more facile at reading and interpreting codes--and then applying color--than it is at going in reverse...seeing color and knowing what it means. For you as a human, presuming you're not color blind, there's no difference....but we're asking Excel to do the work.
So that single table would look something like this:
Situation DayNight Rate 1 D 20 1 N 35 2 D 15 2 N 30 I'm going to leave that for now and ask that you confirm that this makes sense.
Second, though, what do the entries in the red and blue cells mean? 03/abr etc. What does the "abr" refer to? Are there other codes that could appear?
In general, it's not a good idea to mix numeric and alpha characters in a single cell, especially if (as I think is the case) you're expecting mathematical calculations to be made based on the numbers. So if these are simply codes, that's fine, but if that means "3 hours ...." and the hours are going to be multiplied by the appropriate rate, then you'll need to find a different way to register that.
Let's leave aside for the moment the sequence of casting workers and just try to figure out how this pay process is toadministered.
- Well I might've got ahead of myself. Sorry!
First, the table makes sense. We have 4 different outcomes so to speak.
Second, "abr" is short for "ABRIL" which is APRIL in portuguese, so I want to fill in the cells with the day they are cast for the work for personal management. So I only move to the next cell (from C2 to D2 when C2 is filled with a date, in this example) when the previous one has a date on it.First, the table makes sense. We have 4 different outcomes so to speak.
Glad to hear that.
But I think the next part, where you enter the specifics about each of three workers, needs some clarification.
- Although you use color to differentiate between day and night, you don't give any indication in that part of your first sheet as to which of the two scales (each of which has its own day and night rate) is applicable. So I've created a different way to display the assignments, and strongly recommend that instead of using 03/abr to designate the 3rd of April, you use 4/3/22 (or 3/4/22, whichever is your country's normal way of displaying dates. That will enable easy data manipulation when we get to that stage of calculating pay for a given period.
- Second, it's not at all clear how you get from Worker 1's first four periods of night time work to the pay of 325 Euros. What was the mathematical calc there? Neither the 35Euro rate nor the 30Euro rate relates directly to 325....so there must be another unmentioned variable buried there--perhaps an hourly rate.
So I've created a working spreadsheet for you....please fill in the two columns as requested (or explain why something else is needed). I'm hoping you can post it back in this thread; if not, we may need to use OneDrive or GoogleDrive or SharePoint for you to post the revised file.
