@TerryN1963
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.
- Cell A1. Enter .50
- Cell B1: enter the formula =A1*1.5 [with the result being 0.75)
- Cell C1: enter the number of hours, 3120
- Cell D1: enter the formula =B1*C1 [with the result showing as 2,340]
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.
