Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with a formula involving 3 columns
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 |
#2
|
|||
|
|||
=IF(C2<1,IF(AND(OR(D2="D",D2="E",D2="F"),OR(E2="VV S1",E2="VVS2")),J2-J2*.04,
J2-J2*.15),"") -- HTH RP (remove nothere from the email address if mailing direct) "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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Na, Na, Na Bob, you left out the 0.2 of the 1.2!<bg
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Bob Phillips" wrote in message ... =IF(C2<1,IF(AND(OR(D2="D",D2="E",D2="F"),OR(E2="VV S1",E2="VVS2")),J2-J2*.04, J2-J2*.15),"") -- HTH RP (remove nothere from the email address if mailing direct) "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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#8
|
|||
|
|||
Lol. There is always a 'gotcha' :-)
Bob "RagDyeR" wrote in message ... Na, Na, Na Bob, you left out the 0.2 of the 1.2!<bg -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Bob Phillips" wrote in message ... =IF(C2<1,IF(AND(OR(D2="D",D2="E",D2="F"),OR(E2="VV S1",E2="VVS2")),J2-J2*.04, J2-J2*.15),"") -- HTH RP (remove nothere from the email address if mailing direct) "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 |
#9
|
|||
|
|||
Jon,
I am guessing a bit here, but it seems to me that you need a table of values that you look up once you have ascertained that the colour and clarity are in range. =IF(AND(OR(D2={"D","E","F"}),OR(E2={"IF","VVS1","V VS2"})),J2-J2*LOOKUP(C2,{0 ,1.1,1.2;0.085,0.096,0.02})) uses a table of values where C2<1..1 - multiplier of 0.085 C2<1.2 - multiplier of 0,096 else: multiplier of 0.02 adjust the table limits and multipliers to suit and add others. -- HTH RP (remove nothere from the email address if mailing direct) "GoodOldGold" wrote in message ... Argh... running into another lil prob. I took the formula you guys gave me and when I enter it by itself it works fine. However when I duplicate the formula to cover differnt qualities and add it to the formula you gave me I get an error. Can I copy/paste both of these formula's together to put in one cell to accomplish my goal somehow or does an entirely different formula have to be written? Projected Wholesale Cost Formulas Formula for 1ct-1.19c D-F VVS1-2 =IF(AND(C2<1.2,OR(D2={"D","E","F"}),OR(E2={"IF","V VS1","VVS2"})),J2-(J2*0.04 ,J2-(J2*0.15) Formula for 1-1.19ct D-F VS1-2 ,IF(AND(C2<1.2,OR(D2={"D","E","F"}),OR(E2={"VS1"," VS2"})),J2-(J2*0.08,J2-(J2*0.15) If I could get a phone call or better yet an email from either I am willing to pay for your time in helping me solve this. I'd email you the spreadsheet and price structure to help. My email is . Thanks in advance Jon "GoodOldGold" wrote: 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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
Hi Jonathan,
I believe that neither I nor RD work for MS (I know I don't, and I think MS employees have to identify themselves if they participate), we just do it for kicks. Believe it or not, it can be fun, and by watching the groups, we invariably learn something new as well. As to your problem, I attempted a reply earlier today, but I guess from this that I was off-mark I allowed for different weights, but not clarities. What about different colours? The best help would be if you could decision a decision table showing all of the combinations, and what discount they generate. If you can do that, or maybe give examples of each combination with discount (don't forget the 'any other case' situation), I would be happy if you want to send me the workbook, and I will create it for you. My email bob dot phillips at tiscali dot co dot uk do the obvious with it.. -- HTH RP (remove nothere from the email address if mailing direct) "GoodOldGold" wrote in message ... 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 |
#12
|
|||
|
|||
Just popped ya the email.
"Bob Phillips" wrote: Hi Jonathan, I believe that neither I nor RD work for MS (I know I don't, and I think MS employees have to identify themselves if they participate), we just do it for kicks. Believe it or not, it can be fun, and by watching the groups, we invariably learn something new as well. As to your problem, I attempted a reply earlier today, but I guess from this that I was off-mark I allowed for different weights, but not clarities. What about different colours? The best help would be if you could decision a decision table showing all of the combinations, and what discount they generate. If you can do that, or maybe give examples of each combination with discount (don't forget the 'any other case' situation), I would be happy if you want to send me the workbook, and I will create it for you. My email bob dot phillips at tiscali dot co dot uk do the obvious with it.. -- HTH RP (remove nothere from the email address if mailing direct) "GoodOldGold" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
How do I drag a formula from right to left columns instead of rig. | Excel Discussion (Misc queries) | |||
how to build a formula to match numbers in 2 columns with the equ. | Excel Worksheet Functions | |||
Req Formula to place 1 into next columns | Excel Worksheet Functions | |||
Req Formula to place 1 into next columns | Excel Worksheet Functions |