Amount of Increase of Wages

[dah]

Hi:

I am trying to write a formula to calculate a annual amount of increase
of wages.

For example: I have a list of Current Wage rates. I also have a list
of proposed wage rates. I want to take the difference between the two
columns *2080 (if its an hourly person) or *1 (if a salary person).
Then, I want a total of this calculation for all the rows in the
columns.

So, if I have 4 people getting an hourly wage increase of $.50 (which
is the difference between the two columns) and I have 1 person getting
a $1000 per year increase, the number I am looking for should calculate
to be $5160 - annual amount of increase in wages.

Any thoughts?

Deb

 

[RagDyer]

Old rate in A2 to A100.
New rate in B2 to B100.

Assume that *no hourly* person is making $100/hr.
Assume *all salaried* persons are making *more* then $100/yr.

=SUMPRODUCT((B2:B100<100)*((B2:B100-A2:A100)*2080)+((B2:B100>100)*(B2:B100-A
2:A100)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[dah]

Thanks. I had created an IF statement but it was fairly long. I'l
give this a try.

De

 

[dah]

A new question:

What if I have hourly and salary in the same column. Hourly is listed
as hourly rate and salary is listed as salary. The SumProduct doesn't
seem to work for that, only when all rates are listed as hourly -
meaning the salary people would have to be broken down to an hourly
rate.

Any more thoughts?

Deb

Example:

Old Rate New Rate
10 10.50
11 11.50
35000 36000
25000 27000

 

[dah]

One more question regarding a previous answer:

=SUMPRODUCT((B2:B100<100)*((B2:B100-A2:A100)*2080)+((B2:B100>100)*(B2:B100-A2:A100)))

What does B2:B100<100 mean. What exactly is this calculating or what
has to be less than $100.

Deb

 

[BenjieLop]

One more question regarding a previous answer:

=SUMPRODUCT((B2:B100<100)*((B2:B100-A2:A100)*2080)+((B2:B100>100)*(B2:B100-A2:A100)))

What does B2:B100<100 mean. What exactly is this calculating or what
has to be less than $100.

Deb

This is one of the assumptions that RD made in his formula. He assumed
that the hourly workers' wages are less than $100. In other words, if
your column entry is, say, $10.50 (which is definitely less than $100),
then your conditions to calculate for hourly workers apply.

Applying RD's assumption in the table that you presented:


Old Rate New Rate
10 10.50 ----- these are hourly rates (since entries are less
than $100)
11 11.50 ---- same as the above
35000 36000 ----- these are salaries (since entries are greater than
$100)
25000 27000 ---- same as the above

Again, these are just assumptions. You can make your own as long as
they are consistently applied to your formula.

Hope this helps you.

Regards.

 

[RagDyer]

The formula is made to subtract all values in Column A (old, lower rate),
from all values in Column B (new, higher rate).
Then, where Column B is less then 100 (no one is making $100/hr.), multiply
that remainder by 2080,
And, where Column B is greater then $100 (every salaried person is making
*more* then $100/yr.), just add that remainder to the multiplied remainders
from the rest of Column B.

So, it works with hourly rates of less then $100, *AND* yearly salaries of
over $100.

So YES, you can mix the two types of pay in the same Column B, and receive
the sum you're looking for.
--
Regards,

RD

 

[dah]

I got it. I was working to hard. Originally the formula didn't appear
to work but I was taking the salary amounts (say 30000) and converting
it to an hourly rate. Then the salary part of the formula didn't work.
After looking at this for a while, I realized my mistake and everything
works fine now.

Thanks for the help.

Deb

 

[dah]

Appreciate all the comments, Thanks.

De