Multiply Cell by Two Different Numbers Based on Range

[brandon12]

I am attempting to create a formula in B6 that considers the following criteria:

If SUM B1:B2 <100

• then SUM(B1:B3)*28

If SUM B1:B2 >100

• then multiply the first 99 hours by $28 and every hour worked thereafter by $32. Then add B3*28.

* B1 & B2 are the only hours to be considered regarding < or > criteria (not B3).
** This is a large spreadsheet that I cannot add-in any additional tables for reference. I need a formula that references only current established cells.

Any assistance is greatly appreciated!

 

[jasonb75]

Simple method based on the example provided.

=SUM(B1:B3)*B4+MAX(0,SUM(B1:B2)-99)*4

Note that the multiplier of 4 at the end of the formula is actually $4.00 ($32.00-$28.00) or the difference between the two hourly rates.

 

[brandon12]

Simple method based on the example provided.

=SUM(B1:B3)*B4+MAX(0,SUM(B1:B2)-99)*4

Note that the multiplier of 4 at the end of the formula is actually $4.00 ($32.00-$28.00) or the difference between the two hourly rates.

When I enter the formula you provided it provides me with $12,793.00. I calculated manually and the formula should compute $3,188.00.

 

[Marcílio_Lobão]

brandon12, Good evening.

The formula from jasonb75 is a good formula. Very clever!

Just a little TYPO: Change B4 to B5 in the formula.

I hope it helps.

 

[brandon12]

brandon12, Good evening.

The formula from jasonb75 is a good formula. Very clever!

Just a little TYPO: Change B4 to B5 in the formula.

I hope it helps.

Aww. Yes indeed that was the issue. Thanks for finding that.

 

[brandon12]

Simple method based on the example provided.

=SUM(B1:B3)*B4+MAX(0,SUM(B1:B2)-99)*4

Note that the multiplier of 4 at the end of the formula is actually $4.00 ($32.00-$28.00) or the difference between the two hourly rates.

Thanks Jason! After changing B4 to B5 I obtained the answer I was looking for.

 

[jasonb75]

Oops, my bad
When I copied the example over to excel to do a quick test on the formula I missed the 'Total hours' and moved the hourly rate up in error.
@Marcílio_Lobão thanks for correcting my mistake