Nested IF not working - Please help - thx

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
I J K L M (NOPQ) R S T U V W X Y
Ordered Product SKU Ordered Comm Completed Pack Single Potato PO# Packaging Comments Bags/min Production Time
Quantity Description Netweight Variety Order (x) on Line Units

800 10/5 lb. Ahold Russet USA (40034) R400 40000 POT RUS 3 8000 39 205
200 10/5 lb. Ahold Red USA (40015) RED284 10000 POT RED 2 2000 39 51
400 5/10 lb. GV Russet USA (279636) R442 20000 POT RUS 5 2000 30 67
200 10/5 lb. Ahold White USA (40002) W347 10000 POT WHT 5 2000 30 67
2 Per Pound White Fresh Peeled Whole A Size . . FF111 2 FCP WHT
54 Fingerlings . B Size 12/1.5lb Steam Carton . Side Delights
Steamables Steamable US #1 . F186 972 POT FIN 7 648 58 11
54 Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton .
Side Delights Steamables Poly Roll US #1 . MDLY114 972 POT MDLY 6 #VALUE!
12 Mix . C Size 2 Var* 6Packs/ 1lb Side Delight Roastable Carton
Misc. Side Delight Roastable Roastable Tray US #1 . MIX153 144 POT MIX 5 #VALUE!

I placed column labels above headers to add clarity to explanation
The table is the construction of a rudamentary production schedule and also defines line load (I did not show the latter as it is not tied to the formulas).

The following formulas are in above excel table:
Column T: =IF(ISNUMBER(SEARCH("*POT*",M2)),SUM(I2*LEFT(TEXT(J2,"???/???"),FIND("/",TEXT(J2,"???/???"))-1)+0)," ")
Column X: =IF(S2>0,IFERROR(VLOOKUP(J2,ProductArray,10,FALSE)," ")," ")
Column Y: =IFERROR(IF(X2>0,SUM(T2/X2)," ")," ")
(NOPQ) are BLANK columns. No specific reason. This is due to the export of the table from a database. These are HIDDEN in the Excel sheet.
All other columns or data are NOT impacted by any formula, but are required for other non-formula issues or data entry.

Issue:
I am able to get columns X and Y to populate. They use a VLOOKUP table and then calculate. All is good there!
Column T is my issue. Column T works well until there are mulitple "/" in column J. I want the formula to find the NUMERATOR from within the description, then multiply that numerator by column I thus calculating a whole number. For instance: In the first line of the table above, I am able to successfully calculate finding the numerator from 10/5 lb. as 10 and then multiply by the 800 noted column I and return the value 8000 in column T.
When I attempt to do the same where column J has mulitple examples of "/", some used as separators, and one used as Numerator. An example where the statement does NOT work is the error #value! in column T. In this case, the file is seeing mulitple "/" in the description "Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton .Side Delights Steamables Poly Roll US #1". I want the formula to pull the numerator from 12/1.5 lb and return 144 (i.e 12 * 54= 144) and ignore the separators in "Medley RED/YELLOW/PURPLE".

How do I single out ONLY the numerator and not the separators within the formula noted in column T?

Second issue is similar. How do I separate the number from the product description "Mix . C Size 2 Var* 6Packs/ 1lb Side Delight Roastable Carton Misc. Side Delight Roastable Roastable Tray US #1". Instead of the #value error noted on the last line of the above table, I want column T to cull the 6 from "6 packs/1 lb" and multiply that by column I, in this example...12; so that Column T would return 72 (i.e. 6*12 = 72). The issue here is the text immediately before "/".

I hope this makes sense. I can make it work when there is ONLY a numerator in the description, but am challenged when separators AND numerator exist within the description. Thanks in advance for the assist. I need this to be formula and not SQL or code. Ultimately this will upload to Google Sheets for mulitple department, real time, updates for any column currently blank.
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Table lost clarity upon posting. I believe and hope my question is still understandable. Sorry. Not sure how to correct.
 
Upvote 0
Grab the Forum Tools add-in for Excel (link in my signature line) and repost your table using that.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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