07-02-2020 12:14 PM - edited 07-02-2020 12:28 PM
07-02-2020 12:14 PM - edited 07-02-2020 12:28 PM
I am new to excel. I have made a spreadsheet to calculate wage increases. I am trying to make a formula that calculates the overtime. In other words, I want to put in the number and have the cell calculate it to 1.5x the rate (e.g. $0.50=$0.75). There is more to it than just that and I will explain below.
There are three columns and one row involved. Let's say column A, B, and C; and row 1.
Column A1 will be the place I insert the wage increase (e.g. $0.50).
Column B1 will be the static number of hours for the calculation (e.g 3120).
Column C1 will be the subtotal.
I need a formula that will do this:
When I put the $0.50 into column A1 it will be converted into 1.5X the the rate increase or $0.75, and then the $0.75 will be multiplied by B1 (3120) and the outcome placed into C1 which would be $2340.00.
I thought this might be done by some type of nesting formula but I have had no success.
07-02-2020 01:31 PM
You are tossing terms around here in ways that are potentially confusing. Having been in HR for my career, having learned my way around spreadsheets (Lotus 1-2-3 in my HR days), I'm concerned first with wanting to make sure we're really not only precise, but also accurate in the description of what you want to calculate.
You being with a sentence saying you're creating "a spreadsheet to calculate wage increases." In the HR world, "wage increases" would refer to the growth of the base rate of pay, right?
But in the next sentence you jump to making "a formula that calculates the overtime." Are you equating wage increase, in this case, with calculating overtime?
And then there's the static number of hours. In your example, 3,120. If we assume a 40 hour workweek, that amounts to 78 weeks, far more than a year. Or if we spread those hours over 52 weeks (meaning no time for vacation?!) it comes out to a consistent 60 hour work week. Really?! Is that really what you want?
As @Sergei Baklan hints in his earlier reply, there's a lot of clarification needed. The basic formulas to do these kinds of calculations are pretty simple, but the important thing is to make sure you can spell out in common words an accurate (as well as precise) [the two words don't mean the same thing] description of the operation(s) to be performed.
If I were to go with what you wrote, I would first point out that although yes, you could do a lot of nesting, but (especially for beginners with Excel) it's generally better at the start to not go for the fancy nested (and hard to decipher) formula; rather break it into its steps. In doing so, I'd add a column or two. So let's do this.
Now, you can change the numbers in Cells A1 and C1 to your heart's content. The results will vary correspondingly.
Frankly, I think most Excel experts would suggest that you store the value 1.5 somewhere else as well, instead of "hard-coding" it into a formula. So let's say you store that over in cell O1 ("O" for "Overtime"). In fact you can even name that cell "OT_Rate" and then whenever businesses change the OT_Rate from 1.5 to, say, 1.55 or 1.6, all you'd need to do is change the rate in that single cell and it would automatically update all calculations made using it.
Then you could write the formula in cell B1 as =A1*OT_Rate or =A1*O1. The rest would stay as they were.
But please, before you go and complete your spreadsheet, come back and let @Sergei Baklan and me know whether we've totally missed the mark.
And maybe then we can talk about "nesting." Quite seriously, though, a nested formula is fun to write, but it can quickly become hard to read, and it's easy as a result to make big mistakes without realizing it.
07-02-2020 01:45 PM
I am a union representative and negotiate labor agreements. I am wanting to calculate "new money." In other words, I want to see how much the wage increase per year will be realized in a member's paycheck.
I believe you are right. I will make a new column to get this done, instead of trying to make a difficult formula. Also, it will be easier for a person to understand when looking at it because the work will be shown.
07-02-2020 02:11 PM
You wrote: I will make a new column to get this done, instead of trying to make a difficult formula. Also, it will be easier for a person to understand when looking at it because the work will be shown.
Exactly. And if you use "named ranges" for your calculations, it makes it easier still to see what the formula is doing. You could create a table off to the side ... and use the labels to name the ranges (look it up in Excel Help)
Then your formulas are entirely intelligible, AND you can do "what-if" by changing the assumptions and seeing the results without changing the formulas. See the attached. Here's an image, but the attached Excel file is "live".