Hello, I have a mult conditional formula
Condition 1 is value imperial (value changes based on a pick list)
Condition 2 are values less than (5000 Imperial) or (metric 34.47)
Condition 3 do values meet criterias and rate result (excellent, very good, good, fair, poor)
Condition 4 If value is metric (follow same logic as condition 2 and 3)
More notes to help explain the issue in the workbook. Hopefully you can see the workbook attached?
Last edited by AliGW; 01-07-2022 at 05:44 PM. Reason: Tagged as solved - no need to edit the thread title!
The paper clip icon does not work for attachments. To attach a file:
- Under the text box where you type your reply click the Go Advanced button.
- On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
- Click the Choose File button to select a file to attached.
- Click the Upload button to upload the file.
- Then click the Close This Window button. Your file is now attached to your post.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Ok, thank you, I missed the upload button to the far right.
You can see the formula fail on the workbook if you select 80 on the test value pick list
There is still no file attached.
Third times a charm...I hope! Thank you for your help
Last edited by BossZues; 01-06-2022 at 01:25 AM.
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Your data and tests make no sense: I4:I8 have values of 1 to 5 yet you are testing for values in the range of < 400 and >700 if "Imperial"
and
if "Metric" IF(I4<=2.7579,"Excellent",IF(AND(I4>=2.7579,I4<=3.44738),"Very Good"
for tests such as
IF(I4<400,"Excellent",IF(AND(I4>=400,I4<=500),"Very Good",........
this will suffice
IF(I4<400,"Excellent",IF(I4<=500,"Very Good",.....)
this
,IF($E$6>=34.47,IF(J4<=7%,.........
only gets executed if the highlighted below is False
,IF(I4>=4.82633,"Poor",IF($E$6>=34.47,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Ok, great, thank you very much!
I cleaned up the formulas significantly with your input and tested. The formula works for the first string but not second (%). I still get a FALSE error.
The formula has four primary conditional strings (2 for Imperial and 2 for Metric).
For ease of discussion lets only focus on Metric values and testing formula. I have both since the workbook will hold one or the other in the future and we want the ability to pick which type of workbook it will be and the formula will adjust.
The formula works for the first part of the metric string but not when the 2nd primary string (%) condition is met (X>34.47). If you change cell E6 (X factor) the formula returns FALSE.
Last edited by BossZues; 01-06-2022 at 11:18 AM.
Updated my profile with clearer excel info, thank you
Last edited by BossZues; 01-06-2022 at 11:40 AM.
As I stated before ..
IF($E$6>34.47,IF(J8<7%,"Excellent",IF(J8<=9%,"Very Good",IF(J8<=11%,"Good",IF(J8<=14%,"Fair",IF(J8>14%,"Poor"))))))))))))))))
This logic NEVER gets executed. Are columns I and J independent so testing I could return "Excellent" but J returns "Very Good" ?
I would look at replacing the IFs with "lookup" table.
Last edited by JohnTopley; 01-06-2022 at 03:31 PM.
That is correct thge logic from one string or the other will not be used. It depends on the value selected from the picklist in cell (E6).
If the value in cell (e6) is below 34.47 the values in coulumn (I) are used and if the value in cell (e6) is greater than 34.47 the values in column (J) are used. In real world the value in cell E6 represents design strength and the values in column I and J are from material strength tests. Column (I) are Metric strength MPA, and column (J) is (% variance) from materials strength tests.
Yes, columns I and J are independant values and the formula needs to differentiate which one to look at based on the design value selected in cell (E6). Its a pick list of strength values in real world for an example.
I like the idea of vlookup. Im going to try and build it out in the morning and will let you know how it goes.
I really appreciate your time helping me with this. Its a very nice thing to do, thank you!
VLOOKUP Version
=IF(J4="","",IF(J4=0,"",IF($E$3="Imperial",IF(E6<=5000,VLOOKUP($I4,$E$13:$F$17,2,1),IF($E$6>5000,VLOOKUP($J4,$H$13:$I$17,2,1))),IF($E$3="metric",IF($E$6<=34.47,VLOOKUP($I4,$K$13:$L$17,2,1)),IF($E$6>=34.47,VLOOKUP($J4,$H$13:$I$17,2,1))))))
Thank you for the vlookup formula. I tested the formula but still get the same "false" (error). Its doing the same error as the other formula method at the same point in the string execution.
If you change cell (E6) so its greater than (5000 for Imperial) or (34.47 for Metric) it returns false. It should return one of the values specified in the table but it does not. The workbook provided with the new vlookup formula also shows false result in colum (L). If you enter a value in cell (e6) to greater than 5000 for imperial or 34.47 for metric it fails to return a value?
Last edited by BossZues; 01-07-2022 at 02:50 PM.
Added workbook to show error with vlookup when cell e6 has greater than condition met
=IF(J4="","",IF(J4=0,"",IF($E$3="Imperial",IF(E6<=5000,VLOOKUP($I4,$E$13:$F$17,2,1),IF($E$6>5000,VLOOKUP($J4,$H$13:$I$17,2,1))),IF($E$3="metric",IF($E$6<=34.47,VLOOKUP($I4,$K$13:$L$17,2,1)),IF($E$6>=34.47,VLOOKUP($J4,$H$13:$I$17,2,1))))))
Try
=IF(J4="","",IF(J4=0,"",IF($E$3="Imperial",IF(E6<=5000,VLOOKUP($I4,$E$13:$F$17,2,1),VLOOKUP($J4,$H$13:$I$17,2,1)),IF($E$3="Metric",IF($E$6<=34.47,VLOOKUP($I4,$K$13:$L$17,2,1),VLOOKUP($J4,$H$13:$I$17,2,1))))))
Wooohooo! It works!! Thanks for your help! It works!!
Thank yoU!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks