gross margin if statement

[roxyr]

Hi,

I've been pondering this for an hour and have come to the realizatio
that i won't be able to feel the satisfaction of figuring it out on m
own...in other words I'm stuck and need help.

The spreadsheet calculates gross margin. The Standard cost spreadshee
has country names and rates and PDR spreadsheet has a drop down wit
the corresponding name that when selected populates the cost column.
When a rate is entered into the rate coloumn, the calculatio
poplulates the GM (gross margin) column with either the gross margi
percentage when the rate is higher than 4% or a rate not approve
comment when it's lower than 4%.

The problem is that the rule has now changed, not all countries are 4%
some are now 3%.

There are only a handful of country names that the new 3% rule applie
to such as Australia. How can I modify the formula to specify wha
countries are under 3% rule and what countries are under the 4% rule?

the formula is: =IF($C$7="USD",VLOOKUP(B12,'Standar
Cost'!$A$8:$G$588,6,0),(VLOOKUP(B12,'Standar
Cost'!$A$8:$F$588,6,0))*$K$8)

Any assistance is greatly appreciated

Attachment filename: sample.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=58119

 

[Harlan Grove]

...
...

The problem is that the rule has now changed, not all countries are 4%,
some are now 3%.

There are only a handful of country names that the new 3% rule applies
to such as Australia. How can I modify the formula to specify what
countries are under 3% rule and what countries are under the 4% rule?

the formula is: =IF($C$7="USD",
VLOOKUP(B12,'Standard Cost'!$A$8:$G$588,6,0),
(VLOOKUP(B12,'Standard Cost'!$A$8:$F$588,6,0))*$K$8)

...

Best to list the 3% countries in a single column, multiple row range somewhere
else in your workbook, name that range something like CountriesAt3Pct, then if
the country name were entered in a cell named CountryEntry, you could multiply
the formula above by

IF(COUNTIF(CountriesAt3Pct,CountryEntry),0.75,1)