Looking for an Excel Guru to help with a formula....

[MJ]

:sad: :ninja:
Looking for help on creating a formula that incorporates
the following procedure:

You have 5 columns. Column B contains a letter or happens
to be blank: either "p", "c" or "m"; The next set of
columns are all numeric values: Column F contains "True
Cost", Column G contains "Manufacturing Cost", Column H
contains "Vendor Cost", Column K contains "2002 List".

Here's what needs to be done:
ONE FORMULA NEEDS TO BE CREATED SO THAT THE FOLLOWING
CONDITION WILL HAPPEN.

If Column B = "m" or "c" Then Subtract Column G from
Column K and Divide by Column K and then multiply this by
100 to get a percentage.

If Column B = "is blank" Then Subtract Column F from
Column K and Divide by Column K and then multiply this by
100 to get a percentage.

If Column B = "p" Then Subtract Column J from Column K and
Divide by Column K and then multiply this by 100 to get a
percentage.


Example of Values:

Column B ="p", "c", "m" or blank
Column F = $390
Column G = $115
Column H = $88
Column K = $400

Here is what I came up with, but it doesn't work =
@IF(B2='p',($K2-$J2)/($K2)*100),@ IF(B2='m',or 'c',((($K2-
$G2)/($K2)*100), @IF(B2=ISBLANK,((($K2-$F2)/$K2*100)))

If you can assist me I would greatly appreciate it. Thank
you.
:ty:

 

[Dan E]

=IF(OR(B1 = "c", B1 = "m"),(K1-G1)/K1*100,IF(B1="p",(K1-J1)/K1*100,IF(ISBLANK(B1),(K1-F1)/K1*100, "Incorrect Input")))

Dan E

 

[jr]

without even checking the logic, why the @ signs? Drop
the @ signs first. Also, I assume that the blank rows
have values in spite of the lack of a p, m or c.
Otherwise, you get a divide by zero error.

 

[Stephen Dunn]

Hi MJ,

You don't say what should happen if column B contains a value different from
those that you list. On the basis that this is caught by validation, this
formula will work:

=100*($K2-IF($B2="",$F2,IF($B2="p",$J2,$G2)))/$K2

HTH
Steve D.