• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scheduling

D

dolleemag

New Member
Can you help me break this formula down?

=IF(L67=999,0.5,IF(L67=555,0.25,IF(L67>0,(M67/((VLOOKUP(L67,MATRIX!B:I,6)*VLOOKUP(L67,MATRIX!B:I,7))*60)),"")))


What is it telling me? this formula relates to scheduling a part number (L67) and based on quantity ( which I have to manually enter), rotations per minute and how many cavities the machine uses it returns how long a job will take.


I don't understand the .5 and the .25. is the formula deducting or adding these times in? I do understand the *60 relates to minutes in the hour.


The matrix is the tab I am using to look up the corresponding part values.


Please help me!
 
Hi, dolleemag!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


I don't know if anybody without mechanical or engineering skills would be able to help you and even less without having the full context, what does it's used for, data ranges of worksheet Matrix, other formulas, etc.


If you want, consider uploading a sample file, it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Hi ,


I do not know if this will help , but I can break down the IF statement for you !


Let us assume this formula is in cell A1 on the sheet tab labelled CALCS.


The IF statement says :


If the cell L67 contains the value 999 , then put the value 0.5 in A1

ElseIf the cell L67 contains the value 555 , then put the value 0.25 in A1

ElseIf the cell L67 contains any other positive value , then put the result of the following calculation in A1 :


M67/((VLOOKUP(L67,MATRIX!B:I,6)*VLOOKUP(L67,MATRIX!B:I,7))*60


What the above calculation does , can be understood if we go through its evaluation , step by step ; in order to do this , let us enter some data in the different cells that are involved in its evaluation :


L67 itself contains a part number ; the formula considers 3 cases of part numbers viz. 999 , 555 and any other positive number.


M67 contains some number ; only you can say what its significance is ; probably the column headers can give you an idea. Suppose we enter the number 33 in M67.


We have a sheet tab labelled MATRIX which contains data related to part numbers ; the part numbers need to be in column B , and the related data is in columns C , D , E , F , G , H and I. The above formula is concerned with the data in columns G and H ( the 6 and 7 in the VLOOKUP function refer to these columns ).


Let us assume that the part number entered in L67 has a match in cell B18 on the sheet tab labelled MATRIX ; let us assume that B18 has the value 345 , G18 has the value 37 , and H18 has the value 17.


Now you can evaluate the formula in A1 , by putting the value 999 in L67 :

A1 should display 0.5


Change the value in L67 to 555 :

A1 should display 0.25


Change the value in L67 to 345 ; since this has a match in cell B18 on the sheet tab labelled MATRIX , the formula will do the following :


Divide the value in M67 on the sheet tab labelled CALCS , by the product of the numbers 37 ( retrieved from G18 ) , 17 ( retrieved from H18 ) and 60 i.e. 37740. Since M67 itself has the value 33 , the result will be 0.000874403815580286.

A1 should display 0.000874404


Change the value in L67 to any other positive value ; depending on where in the data table a match is found , the result should change accordingly.


Change the value in L67 to either 0 or any negative value.

A1 should display a blank


Narayan
 
You must log in or register to reply here.
Back
Top