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% |
IF FACTOR?
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 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% |
IF FACTOR?
"Bernadette" wrote in message
... 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 |
All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com