IF FACTOR?

How would I create a formula to do the following:
I need to multiply figure A by the correct % based on the figures in B and C
A)$177,740 B) 38.3% C) 41.79
Ex- If B is 38.3% I would need to us the % rate based on GM% less than 40,
however,because C is 41.79 that is greater than 40 so the multiplier would
be 6.0%
The end result I would need is $177,740 X 6%. To get the 6% figure I need to
determine by the charte below, what % to use.
(B) ********(C)*********
GM% >20 20-40 >40
<35 3.0% 3.5% 4.0%
<40 4.0% 5.0% 6.0%
<45 5.0% 7.0% 9.0%
<50 6.0% 9.0% 12.0%

Hi Bernadette,


You can do this by using a vlookup function with variable offset ( third
parameterof the function).

Since it is more difficult to explain that to create, you might send me you
email adress, and I will send you an example worksheet.

Send an email to [email protected] and you will get an answer quickly.

hans

"Bernadette" wrote:

> How would I create a formula to do the following:
> I need to multiply figure A by the correct % based on the figures in B and C
> A)$177,740 B) 38.3% C) 41.79
> Ex- If B is 38.3% I would need to us the % rate based on GM% less than 40,
> however,because C is 41.79 that is greater than 40 so the multiplier would
> be 6.0%
> The end result I would need is $177,740 X 6%. To get the 6% figure I need to
> determine by the charte below, what % to use.
> (B) ********(C)*********
> GM% >20 20-40 >40
> <35 3.0% 3.5% 4.0%
> <40 4.0% 5.0% 6.0%
> <45 5.0% 7.0% 9.0%
> <50 6.0% 9.0% 12.0%
>

"Bernadette" <[email protected]> wrote in message
news:[email protected]...
> How would I create a formula to do the following:
> I need to multiply figure A by the correct % based on the figures in B and
> C
> A)$177,740 B) 38.3% C) 41.79
> Ex- If B is 38.3% I would need to us the % rate based on GM% less than 40,
> however,because C is 41.79 that is greater than 40 so the multiplier
> would
> be 6.0%
> The end result I would need is $177,740 X 6%. To get the 6% figure I need
> to
> determine by the charte below, what % to use.
> (B) ********(C)*********
> GM% >20 20-40 >40
> <35 3.0% 3.5% 4.0%
> <40 4.0% 5.0% 6.0%
> <45 5.0% 7.0% 9.0%
> <50 6.0% 9.0% 12.0%

=A8*INDEX(B3:D6,MATCH(B8*100,A3:A6)+1,MATCH(C8,B2:D2)+1)
where your inputs are in A8, B8, C8, your table of values is B3:D6.
A3 to A6 should contain your 35, 40, 45, 50 values,
I'm assuming that your >20 should have been <20, so I've got 20 in B2, 40 in
C2, and a large number in D2.

Note that you haven't catered for your 2nd input being 50% or above.
--
David Biddulph