don't know which function to use

jgperez78

New Member
Joined
Jul 23, 2014
Messages
13
hi mr. excel, my apologies but i really cannot find which function i should use. this is the string of logic that i am trying to make a formula for. if A2 is <0.04 and B2 is "D-F" and C2 is "IF-VVS" then A2 should be multiplied by 1250. i know its a lot but i just need to know how to make a formula for this one. please help. thank you SO much in advance.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try
Code:
=IF(AND(A2<0.04,B2="D-F",C2="IF-VVS"),A2*1250,"")
 
Upvote 0
hi michael, i tried but it didn't work. my apologies again, i'm very poor when it comes to formulas. this is what i'm trying to figure out.
WEIGHT (ct)COLORCLARITYPRICE in USD$/ct
0.01D - FIF - VVS1250

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

if the weight is less than 0.04 and the color is D-F and the clarity is IF-VVS then the weight should be multiplied by $/ct and the product to be in the Price in USD. thank you SO much again. really appreciate your quick reply.
 
Upvote 0
That's because your text has spaces between the letters and the "-"



Excel 2003
ABCDE
1WEIGHT (ct)COLORCLARITYPRICE in USD$/ct
20.02D - FIF - VVS$ 25.001250
Sheet1
Cell Formulas
RangeFormula
D2=IF(AND(A2<0.04,B2="D - F",C2="IF - VVS"),A2*E2,"")
 
Upvote 0
told you i was bad. i need to continue this string. A2 is equal to 0.05 but less than 0.08, how do i do it? thank you SO much again.
 
Upvote 0

Excel 2007
ABCDE
1WEIGHT (ct)COLORCLARITYPRICE in USD$/ct
20.055D - FIF - VVS$ 68.751250
Sheet1
Cell Formulas
RangeFormula
D2=IF(AND(A2>=0.05,A2<0.08,B2="D - F",C2="IF - VVS"),A2*E2,"")
 
Upvote 0
if i need to add another string to it, how should i do it?
=IF(AND(A2<0.04,B2="D - F",C2="IF - VVS"),A2*E2,""),(AND(A2>0.04,B2="G - H",C2="VS"),A2*F2,"")
is this correct? thank you again so much
 
Upvote 0
Ok, take a step back and repost when you have ALL the required criteria for the formula
 
Upvote 0
Hi Michael, sorry about earlier. I now have all the criteria needed to enter into one formula. I’m not sure if this is possible due to its length. Please find below the conditions to be included in the formula. Thank you SO much in advance.

=IF (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “IF – VVS” then D2 is A2*1250), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “IF – VVS” then D2 is A2*1000), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “IF – VVS” then D2 is A2*750), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “IF – VVS” then D2 is A2*490), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “IF – VVS” then D2 is A2*360), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “VS” then D2 is A2*1000), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “VS” then D2 is A2*850), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “VS” then D2 is A2*680), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “VS” then D2 is A2*420), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “VS” then D2 is A2*300), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “SI1” then D2 is A2*730), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “SI1” then D2 is A2*650), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “SI1” then D2 is A2*580), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “SI1” then D2 is A2*390), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “SI1” then D2 is A2*240), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “SI2” then D2 is A2*600), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “SI2” then D2 is A2*550), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “SI2” then D2 is A2*500), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “SI2” then D2 is A2*350), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “SI2” then D2 is A2*210), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “SI3” then D2 is A2*500), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “SI3” then D2 is A2*460), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “SI3” then D2 is A2*440), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “SI3” then D2 is A2*310), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “SI3” then D2 is A2*180), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “I1” then D2 is A2*460), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “I1” then D2 is A2*430), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “I1” then D2 is A2*420), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “I1” then D2 is A2*260), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “I1” then D2 is A2*150), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “I2” then D2 is A2*400), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “I2” then D2 is A2*380), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “I2” then D2 is A2*350), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “I2” then D2 is A2*220), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “I2” then D2 is A2*130), (A2 is 0.01 to 0.03 and B2 is “D – F” and C2 is “I3” then D2 is A2*330), (A2 is 0.01 to 0.03 and B2 is “G – H” and C2 is “I3” then D2 is A2*300), (A2 is 0.01 to 0.03 and B2 is “I – J” and C2 is “I3” then D2 is A2*270), (A2 is 0.01 to 0.03 and B2 is “K – L” and C2 is “I3” then D2 is A2*160), (A2 is 0.01 to 0.03 and B2 is “M – N” and C2 is “I3” then D2 is A2*100)

If this cannot be done, please show me another way. Thank you SO VERY MUCH in advance.
 
Upvote 0
Yep, I knew that was going to happen !!!!
You'll need to use a VLOOKUP table to get the best result......I've done all the hard work for you !!!



Excel 2007
ABCDEFGH
1WEIGHT (ct)COLORCLARITYPRICE in USD$/ctLookup Table
20.028M - NIF - VVS$10.08360combined codevalue
3D - FIF - VVS1250
4G - HIF - VVS1000
5I - JIF - VVS750
6K - LIF - VVS490
7M - NIF - VVS360
8D - FVS1000
9G - HVS850
10I - JVS680
11K - LVS420
12M - NVS300
13D - FSI1730
14G - HSI1650
15I - JSI1580
16K - LSI1390
17M - NSI1240
18D - FSI2600
19G - HSI2550
20I - JSI2500
21K - LSI2350
22M - NSI2210
23D - FSI3500
24G - HSI3460
25I - JSI3440
26K - LSI3310
27M - NSI3180
28D - FI1460
29G - HI1430
30I - JI1420
31k - LI1260
32M - NI1150
33D - FI2400
34G - HI2380
35I - JI2350
36k - LI2220
37M - NI2130
38D - FI3330
39G - HI3300
40I - JI3270
41k - LI3160
42M - NI3100
Sheet1
Cell Formulas
RangeFormula
D2=IF(AND(A2>=0.01,A2<=0.03),A2*E2,"")
E2=VLOOKUP(CONCATENATE(B2,C2),$G$3:$H$42,2,0)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top