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.

 

[rxharp419]

See post #8 .

I click on that and it brings me to several post. Is there a link to a particluar post i should visit?

 

[Aladin Akyurek]

I click on that and it brings me to several post. Is there a link to a particluar post i should visit?

In I2 enter:

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

where H2 must house a number. If H2 does not house a number or H2 is empty, the result is a formula blank.

 

[rxharp419]

In I2 enter:

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

where H2 must house a number. If H2 does not house a number or H2 is empty, the result is a formula blank.

Hi again,

Yes i understand that. However, I am nesting this IF into another IF as shown here:

=IF(ISBLANK(T8),"",IF(T8="Travel Rate",MIN(8,U8)*V8+MAX(0,U8-8)*W8,(V8)+(W8*U8))). I cant get it to stay blank.

How can I incorporate it into this?

 

[Aladin Akyurek]

What else can T8 be besides Travel Rate?

 

[rxharp419]

It will be "Travel Rate" or "Hrs Past 10"

 

[Aladin Akyurek]

It will be "Travel Rate" or "Hrs Past 10"

Either...

=IF(T8="","",IF(T8="travel rate",MIN(8,U8)*V8+MAX(0,U8-8)*W8,IF(T8="hrs past 10",W8*(1+V8),"")))

Or, if you have the SWITCH function...

=IFERROR(SWITCH(MATCH(T8,{"travel Rate","hrs past 10"},0),1,MIN(8,U8)*V8+MAX(0,U8-8)*W8,2,W8*(1+V8)),"")_{^{<strike>
</strike>}}