LOOKUP HELP!!!!!

  • Thread starter Brian.Hastings82
  • Start date
B

Brian.Hastings82

Hi guys

Im making a billing sheet for a new piece of business and stuck!

I have a DMC % and a COMP %

If the DMC <= 15% = Rate A

If the DMC >15% and COMP <6% = Rate B
If the DMC >15% but COMP >6% = Rate A

I need a formula that looks up the %'s and tells me what rate to use.

Any help would be appreciated!

Cheers

Brian
 
J

joeu2004

If the DMC <= 15% = Rate A
If the DMC >15% and COMP <6% = Rate B
If the DMC >15% but COMP >6% = Rate A
I need a formula that looks up the %'s and tells me what rate to use.
Click to expand...

If it is as simple as that, you do not need a look-up function. You
could write something like the following:

=if(DMC <= 15%, RateA, if(COMP <= 6%, RateB, RateA))

Equivalently:

=if(or(DMC <= 15%, COMP > 6%), RateA, RateB)

Note that I changed "COMP < 6%" to "COMP <= 6%". If you want RateA
when COMP = 6%, change the above to "COMP < 6%" and "COMP >= 6%"
respectively.

DMC, COMP, RateA and RateB can be named cells or ranges, cell
references or constants.
 
B

Brian.Hastings82

If it is as simple as that, you do not need a look-up function. You
could write something like the following:

=if(DMC <= 15%, RateA, if(COMP <= 6%, RateB, RateA))

Equivalently:

=if(or(DMC <= 15%, COMP > 6%), RateA, RateB)

Note that I changed "COMP < 6%" to "COMP <= 6%". If you want RateA
when COMP = 6%, change the above to "COMP < 6%" and "COMP >= 6%"
respectively.

DMC, COMP, RateA and RateB can be named cells or ranges, cell
references or constants.
Click to expand...

Cheers guys, brilliant.
 
Top