Conditional Return Depending on Cell Value

[Cobalt42]

Hello All,

I'm working on a job cost sheet for my service department. They have asked me to have a cell on this sheet to automatically calculate the overhead factor based on the value of the selling price. Essentially multiplying the Total Labor/Materials/Tax/Other cell by different percentages depending on the value of the selling price cell.

For this sheet the information will be in the following cells.

Total Labor/Materials/Tax/Other = C25
Selling Price = C29
Overhead Factor = C26

How they would like it calculated is

If the Selling Price entered is x then multiply Total Labor/Materials/Tax/Other by a predetermined Overhead percent having the result in the Overhead Factor Cell

So if C29 is X then C25 x Percentage = C26

These are the value ranges and the percentages

$5,000 or less = C25 x .30
$5,001 - $10,000 = C25 x .25
$10,001 - $15,000 = C25 x .225
$15,001 - $20,000 = C25 x .20
$20,001 or More = C25 x.175

I hope I've explained what I'm going for here well enough but if you have questions I'll do my best to answer them.

Thanks,

Cobalt42

[WideBoyDixon]

C26 formula:

=C25*IF(C29<=5000,0.3,IF(C29<=10000,0.25,IF(C29<=15000,0.225,IF(C29<=20000,0.2,0.175))))

WBD

[Cobalt42]

Fantastic thank you WideBoyDixon!

[Ace_XL]

Another way

=C25*LOOKUP(C29,{0,5001,10001,15001,20001},{0.3,0.25,0.225,0.2,0.175})