Multiply Two Parts of the Same Number Based on Less Than/ Greater Than.

[rxharp419]

Hello,

I am trying to have a formula multiply two separate parts of a single number that is in a single cell.

i.e. I have the number 14, I want a formula to know that any number less than or equal to the number 8 is to be multiplied by the Straight time rate and any number greater than 8 will be multiplied by the Overtime rate.

I don’t want to have to put straight time in one cell and overtime in another. I would like it to be one number.

I appreciate anyone’s help in advance.

 

[Aladin Akyurek]

It would be very nice if you could show us the manual calculation that is involved when A2 = 14.

​

 

[rxharp419]

It would be very nice if you could show us the manual calculation that is involved when A2 = 14.

​

Hello. I am not sure what you mean. Bascically the cell A1 can be any number, 14, 18, 8... A2 would be the cell with a formula that I am trying obtain. I hope this helps.

 

[Aladin Akyurek]

Hello. I am not sure what you mean. Bascically the cell A1 can be any number, 14, 18, 8... A2 would be the cell with a formula that I am trying obtain. I hope this helps.

You said: "I am trying to have a formula multiply two separate parts of a single number that is in a single cell.

i.e. I have the number 14, I want a formula to know that any number less than or equal to the number 8 is to be multiplied by the Straight time rate and any number greater than 8 will be multiplied by the Overtime rate."

Give a manually calculated example or two... If that's not possible or not intelligible, please ignore my request.

 

[rxharp419]

You said: "I am trying to have a formula multiply two separate parts of a single number that is in a single cell.

i.e. I have the number 14, I want a formula to know that any number less than or equal to the number 8 is to be multiplied by the Straight time rate and any number greater than 8 will be multiplied by the Overtime rate."

Give a manually calculated example or two... If that's not possible or not intelligible, please ignore my request.

[TABLE="width: 848"]
<colgroup><col span="2"><col><col><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 2"]Rates[/TD]
[TD]Total Hours Worked[/TD]
[TD]Total Pay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ST[/TD]
[TD]$50.00[/TD]
[TD]14[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OT[/TD]
[TD]$70.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]I want that 14 total hours worked to be multiplied by 8 straight time hours of pay and 6 overtime hours of pay into cell H3. H3 needs a formula in it that knows how to calculate the first 8 hours out of 14 hours by straight time and any numbers after 8 (14-8=6) in this case which would be 6 multiplied by OT.

I was trying some like IF(H2>=8*A2)AND (H2<8*A3), OR SOMETHING LIKE THIS. I need help to make something work. [/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Assuming that the total hours worked 14 is in H2...

In I2 enter:

=MIN(8,H2)*50+MAX(0,H2-8)*70

 

[rxharp419]

Assuming that the total hours worked 14 is in H2...

In I2 enter:

=MIN(8,H2)*50+MAX(0,H2-8)*70

Hi Aladin!

That actually works great! But, if H2 is blank, the number shows up. Is there a way to have it stay blank with no number if H2 is blank?

 

[Aladin Akyurek]

In I2 enter:

=IF(ISNUMBER(H2),MIN(8,H2)*50+MAX(0,H2-8)*70,"")

 

[rxharp419]

Hi Aladin!

That actually works great! But, if H2 is blank, the number shows up. Is there a way to have it stay blank with no number if H2 is blank?

This is the formula I have: =IF(ISBLANK(T8),"",IF(T8='SGS Wages & Rates'!$B$102,MIN(8,U8)*V8+MAX(0,U8-8)*W8,(V8)+(W8*U8))). I cant get it to stay blank.

If it were a simpler formula like this: =IF(ISBLANK(U8),"",MIN(8,U8)*V8+MAX(0,U88)*W8) - Then I can get it to stay blank. I am unable to with the above, however.

Thank you again for all of your help!

 

[Aladin Akyurek]

See post #8 .