Payroll, various rates simple formula

[JJJMMMCCC]

First Time poster.

Asking for a friend

After a formula or plan:


Need to enter a number of hours into one cell.

What I need is it to calculate from the input of this CELL A to generate result in CELL B:

If CELL A is between and including 0 and 7.6 it needs to display [true value entered]
If CELL A is >7.61 and < 10.61 it needs to display [true value of 7.6] + [1.5* the remainder of the difference of 10.6]
If CELL A is > 7.61 and < 16.00 it needs to display [true value of 7.6] + [3*1.5 true value] + [2.0*The remaining difference of 16]
Notes:
>16.10 needs to error
< 0.00 needs to error

best way I can explain it.
hope it makes sense.

Thanks in advance.

[AliGW]

What does this actually mean?

[true value of 7.6] + [1.5* the remainder of the difference of 10.6]

And this?

[true value of 7.6] + [3*1.5 true value] + [2.0*The remaining difference of 16]

A couple of worked examples, please.

Asking for a friend

Maybe a dating site for this bit???

[JJJMMMCCC]

Hi AliGW

Thanks for the swift reply. I expected my explanation would be difficult to interpret.


1.
workers are paid 1.0 x their rate if they work 4 to 7.6 hours (minimum 4 hours)

2.
if a worker worker, works up to 10.6 hours then they get 7.6 hours at 1.0 times their rate
then
they get paid 1.5 times their rate for 3 hours (maximum 3 hours at 1.5 times their rate)

3.
if a worker, works up to 16 hours they get:
7.6 hours x 1.0 their rate
plus
3.0 hours x 1.5 their rate
plus
2.0 times their rate for the remaing hours up to 16.0 hours.

Note: Rate isnt important but if I could enter their rate as well that would be just a bonus.

Hope that helps clarify.

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[JJJMMMCCC]

sorry the explanation is all I have at the moment.

I'd like to say this if I could make my own formula and it would understand.

assume A1 is the only input cell. with one output cell.

IF A1 is <7.60 then display the true value of A1
IF A1 is >7.61 but < 10.60 then calculate (7.6*1.0) + [(A1 - 7.6)*1.5]
IF A1 is >10.61 but < 16.01 then calculate (7.6*1.0) + (3.0*1.5) + [(A1-10.6)*2.0]

does that help at all?

[AliGW]

IF A1 is >7.61 but < 10.60 then calculate (7.6*1.0) + [(10.60 - 7.6)*1.5]

Why 7.6? Do you really mean the value in A1???

[JJJMMMCCC]

Why 7.6? Do you really mean the value in A1???

Yes sorry.
You're on to it.
But with a minimum value of 4

[AliGW]

IF A1 is <7.60 then display the true value of A1
IF A1 is >7.61 but < 10.60 then calculate (7.6*1.0) + [(10.60 - 7.6)*1.5]
IF A1 is >10.61 but < 16.01 then calculate (7.6*1.0) + (3.0*1.5) + [(A1-10.6)*2.0]

Try this:

=IF(A1<4,"",IF(A1<7.6,A1,IF(A1<10.6,7.6+((10.6-A1)*1.5),IF(A1<16.01,7.6+4.5+((A1-10.6)*2),""))))

[JJJMMMCCC]

thanks for your reply again.
I'm impressed.

It doesnt seem to calculate correctly in the middle part.

I'll put some scenarios to hopefully give the 'work back'

Example 1:
If someone does 6 hours.

6 * 1.0
=
6

The Formula gives
6 CORRECT!!!


Example 2:
If someone does 10 hours.

7.6 * 1 (7.6)
+
2.4 * 1.5 (4.5)
=
12.1


The formula gives:
8.5 INCORRECT


-------------------

Example 3:
If someone does 14 hours

7.6 * 1 (7.6)
+
3 * 1.5 (4.5)
+
3.4 * 2.0 (6.8)
=
18.9

The formula gives:
18.9...CORRECT!!!

so good. I'm not game to mess with your handy work

[AliGW]

Provide a sample workbook with a few lines of dummy data. Put in your expected results - manually calculated. I'm not going to build a dummy workbook for you - that's your job if you want any further help with this.

[JJJMMMCCC]

thanks for your help.
I would be asking for the donation page (and still may) if I can work out the malfunction.
I'll see what I can do from here.
WOW by the way.

thanks heaps. very good at what you do. and quick.

cheers

[AliGW]

There is no need for a donation - this is a free forum!!! Those of us who help here do so voluntarily and because we enjoy it. All I need to resolve the final issue is the sample workbook I've asked for. It should take you no more than five minutes to set it up and post it here for me.

PS If you really want to, you can give somebody who has helped you a virtual 'pat on the back' by clicking on their reputation star under their profile to the left of their post. You can also leave a 'thank you' message that way. If you do that, it's very nice, but it's completely at your discretion. I am just happy to be thanked within the thread in question.

[JJJMMMCCC]

Good reply.

Given you did all the hard work I think I got it now.

This is what i edited the formula to and it seems to work.

=IF(A1<4,"",IF(A1<7.6,A1,IF(A1<10.6,7.6+((10.6-A1)*1.5),IF(A1<16.01,7.6+4.5+((A1-10.6)*2),""))))

=IF(A1<4,"",IF(A1<7.6,A1,IF(A1<10.6,7.6+((A1-7.6)*1.5),IF(A1<16.01,7.6+4.5+((A1-10.6)*2),""))))


So good of you.

Thanks very much.
I'll check out your recommendations.

[AliGW]

If it works for you ...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.