Help with a formula

[GoodOldGold]

Hi all,

I've received some input here which has been helpful but I find myself
running into a bit of a brick wall and I hope someone can help.

Here is my formula.

IF(C43<1.2(AND(D43={"D","E","F"}),AND(E43={"IF","VVS1","VVS2"})),F43-(F43*0.04,IF(AND(C43<1.2,AND(D43={"D","E","F"}),AND(E43={"VS2","VS2"})),F43-(F43*0.08),F43-(F43*0.15)))

To spell it out for ya ... C43 contains a numeric value, D43 contains alpha
numeric characters as well as E43. If columns C, D and E contain certain
values I want the discount from F43 to show certain values in the cell where
I place the formula.

Your help is appreciated.

Jonathan

 

[JulieD]

Hi Jonathan

if i'm understanding you correctly how about
=IF(AND(C3<1.2,OR(D3={"D","E","F"}),OR(E3={"IF","VVS1","VVS2"})),F3-(F3*0.04),IF(AND(C3<1.2,OR(D3={"D","E","F"}),OR(E3={"VS2","VS2"})),F3-(F3*0.08),F3-(F3*0.15)))

Cheers
JulieD

 

[Biff]

Hi!

Hmmm....

AND(E43={"VS2","VS2"})

Is that just a typo? It makes a big difference as to the
true meaning for that section of the formula!

If the above should mean AND(E43="VS2")

Try this:

=IF(AND(C43<1.2,OR(D43={"D","E","F"}),OR(E43=
{"IF","VVS1","VVS2"})),F43*0.96,IF(AND(C43<1.2,OR(D43=
{"D","E","F"}),E43="VS2"),F43*0.92,F43*0.85))

If the above IS A TYPO and should be something like this:

OR(E43={"VS2","VS22"})

Then try this:

=IF(AND(C43<1.2,OR(D43={"D","E","F"}),OR(E43=
{"IF","VVS1","VVS2"})),F43*0.96,IF(AND(C43<1.2,OR(D43=
{"D","E","F"}),OR(E43={"VS2","VS22"})),F43*0.92,F43*0.85))

Biff

 

[GoodOldGold]

You guys rock. I caught the typo Biff. Thank you so much Julie. Last night
I took what you guys gave me and this final formula worked like a charm.

=IF(AND(C45<1.2,OR(D45={"D","E","F"}),OR(E45={"IF","VVS1","VVS2"})),(F45*0.96)*C45,IF(AND(C45<1.2,OR(D45={"D","E","F"}),OR(E45={"VS1","VS2"})),(F45*0.92)*C45,(F45*0.85)*C45))

Now here's another question for ya. This formula covers carat weights
less than 1.20ct ... I'm going to now create a seperate formula for stones
that weight between 1.20 and 1.39ct... The question is ... will I be able to
join the 2 formula's together to cover all weights from 1.00-1.39ct (actually
there will be other formula's for weights 1.40-1.49, 1.50-1.69, 1.70-1.99,
and 2.00-2.49ct.

Would I be best served by using the "Lookup" function (with lists) or can I
somehow meld all these formula's into one line?

Your help is appreciated.

Jonathan

 

[Biff]

Hi!

You would be much better off to build a table and then use
some type of lookup formula. Can't give you any
particulars as I don't have any details.

Biff

 

[GoodOldGold]

Hi Biff,

Thanks for the response. If I were to forward you the details do you think
you could help me out? If so, I am willing to recompense for your help. In
case my email doesn't show up here it's (e-mail address removed)

Thanks,
Jonathan

 

[Biff]

Hi!

Sure, I can help you out, under one condition!

You let me do it for free!

I'll contact you.

Biff

 

[GoodOldGold]

Awesome. I'll be looking out for your email!!!

 

[Ragdyer]

Do it for free Bif ... just ask for a *sample* or two.<g>

And Jonathan,
It would have made things easier and simpler if you had stayed with your
original thread, where all the other folks could see what was suggested
previously.