Hi Rag & Bob,
Thanks again for your prompt reply and help. Just curious do you guys work
for Microsoft to help us poor dudes here or do you do this out of sheet
charity? I also participate and contribute on specialty trade forums and
help consumers with questions as well so it's kinda weird to be on the
receiving end instead of hte other way around. I now appreciate to a greater
degree the help that's offered via online forums. Ok... the details...
Here is the price structure which I am using as my *projected wholesale
costs*. A simple explanation from one line... diamonds weighing from
1.50-1.69ct that are D,E,F colors and VVS1 or VVS2 clarities cost -2% from
the list (which in my formula was column J). Diamonds in that same weight
range that are D,E,F colors and VS1 or VS2 clarities and the discount is -6%
instead of -2%. All other qualities in that weight range are -10%. And so
on ...
Beneath this table is the first 2 rows of the spreadsheet in which I am
inserting the formula.
Weights Special Discount Normal Discount
2.50+ F VVS2+ - 0 -3
2.00-2.49 F VVS2+ -1 F VS2+ -5 -9
1.70-1.99 F VVS2+ -1 F VS2+ -3 -7
1.50-1.69 F VVS2+ -2 F VS2+ -6 -10
1.40-1.49 -5
1.20-1.39 F VVS2 -2 F VS2+ -6 -10
1.00-1.19 F VVS2 -4 F VS2+ -8 -15
..80-.99 -10
..50-.79 -15
..30-.49 -12
IF -5
Shape Size Color Clarity $/Ct $/Ttl Rap/% Rap/Ct Rap/Ttl Projected Wholesale
Round 0.360 H VS2 $1,458.00 $524.88 -19.00% $1,800.00 $648.00 $550.80
I anticipate your reply. If the answer is longer than you care to type or
is too complicated to explain I'm willing to go out and purchase the
necessary book(s) to help me figure this out on my own or am willing to pay
you to set this up for me. I have been working on a project for my business
(revamping/restructuring my Access database) and I have this Excel sheet
linked to Access which processes all the numbers from this Excel sheet into
my invoices etc. and this is one of the final steps to help me complete this
project. I am very appreciative for the help you're giving me.
Kind regards,
Jonathan
"Ragdyer" wrote:
When you're talking that amount of variables, you should get into datalists,
where you then use Vlookup and/or Index-Match functions.
Post back with more details and we'll see how to suggest which might be the
best way for you to go.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"GoodOldGold" wrote in message
...
Guys... I can't thank you enough for your help. Rag... I caught the
missing
.2 ;) You both hit the nail on the head. Another question if I may.
That is a formula I need to use just for sizes from 1-1.19ct in those
qualities. I know how to take this formula and duplicate it for other
sizes/qualities now thanks to you. My question is this ... Can I only use
an
"IF" function 7 times in a formula?
Like if my formula for all sizes and qualities required say ... 15 IF
functions in the formula would I be required to split my sheet up into 2
different sets of sizes?
Are ya following me? If not I'll try to clarify further.
Tremendous thanks,
Jonathan
"RagDyeR" wrote:
Try this:
=IF(AND(C2<1.2,OR(D2={"D","E","F"}),OR(E2={"IF","V VS1","VVS2"})),J2*0.96,J2*
0.85)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"GoodOldGold" wrote in message
...
Wow... great to have found this forum. I pray someone can help.
I'm trying to do what I think is a fairly simple calculation but am not
sure
where I'm going wrong.
I have a list of diamonds along with their clarities, colors, etc. and
the
stones are priced differently according to their qualities. So ...
In one column is the carat weight, one the clarity, one the color and
then I
have a column for projected wholesale. So ... when I enter the formula
in
the "formula helper" thingy I get the proper results there but when I
hit
enter the formula does not appear and says "invalid".
It goes like this ...
Logical test =IF(C2<1.20(D2=D,E,F(E2=IF,VVS1,VVS2))
Value if true J2-(J2*.04)
Value if false J2-(J2*.15)
Ie... my discount for stones that are D, E and F colors combined with
clarities of IF, VVS1 & VVS2 are only 4% back from list price while all
others are 15% back from list.
If someone can help me out with this it would be tremendously
appreciated.
If you can drop me an email letting me know you responded to
that would be appreciated. Otherwise if you
post
here I'll just check. Ah... I see there's an option below to have the
forum
automatically send a response. :) duh Your help is appreciated.
Thanks,
Jonathan