|
Help with a formula
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","V VS1","VVS2"})),F43-(F43*0.04,IF(AND(C43<1.2,AND(D43={"D","E","F"}),AN D(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 |
Hi Jonathan
if i'm understanding you correctly how about =IF(AND(C3<1.2,OR(D3={"D","E","F"}),OR(E3={"IF","V VS1","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 "GoodOldGold" wrote in message ... 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","V VS1","VVS2"})),F43-(F43*0.04,IF(AND(C43<1.2,AND(D43={"D","E","F"}),AN D(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 |
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,F4 3*0.85)) Biff -----Original Message----- 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 . |
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" wrote: 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,F4 3*0.85)) Biff -----Original Message----- 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 . |
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 -----Original Message----- 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" wrote: 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 -----Original Message----- 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 . . |
|
Awesome. I'll be looking out for your email!!! :)
"Biff" wrote: Hi! Sure, I can help you out, under one condition! You let me do it for free! I'll contact you. Biff -----Original Message----- 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 Thanks, Jonathan "Biff" wrote: 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 -----Original Message----- 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" wrote: 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 -----Original Message----- 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 . . . |
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Sure, I can help you out, under one condition! You let me do it for free! I'll contact you. Biff -----Original Message----- 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 Thanks, Jonathan "Biff" wrote: 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 -----Original Message----- 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" wrote: 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 -----Original Message----- 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 . . . |
| All times are GMT +1. The time now is 03:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com