Im not very well use in the use of index and match but for the function i need to do this is the only possible way of doing it with out going into visual basic.
I currently have a table of different configuration, (Draft table) table been sorted by V column largest first
Currently the frame size is looking up the trip current column as that's the one been sorted.
I would like to be able to get rid of these extra fields and just go off the original values (running current and rated amps) and also know how to add additional criteria like Voltage and kA
This is currently give me a value of 82. As you can see i want to know add the voltage any device that has a higher number and also the kA rating any device that is higher
If the current values are use this should give me an answer of 91 (column L)
For the life of me i can seem to this to work and im running out of ideas. Please help
Also the table i would like to be able to add data to it would having to do any sorting, if its possible.
Regards
Daniel
Last edited by l3lackdrake; 07-08-2017 at 07:07 PM.
Hi l3lackdrake, welcome to Excel Forum! A small sample workbook would really help us to solve your problem quickly and accurately.
Remove any sensitive or extraneous info, just give us some data (with headers please) to test. Nobody's going to manually copy it from a picture.
Also, please simulate some results so we know what they look like and where they go.
To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!
The final step is a bit of a hack. I couldn't be certain that the same range of voltages is available to all configurations, so I used the other conditions in a IF() statement to define a configuration, then used MATCH on the appropriate subset. The Voltage look-up column is a secondary descending sort key, so an APPROXIMATE match can be found.
If desired, you could use a separate table or formula to establish the exact voltage and avoid this inconsistent treatment of criteria.
Hope this helps-Lee
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
this works for the most part, when i started to put in a higher kA rating like 25 this should select the fuse line as it set to 1000 for the voltage, frame size, tip and kA but instead i get a N/A so it not finding it.
I also then tried sorting the table by the name and now the formula dose not work at all.
Is there a away to have the table in a no sort envelopment as this will be added to by others and if noting cant be found it returns the answers as fuse?
NOTE: This is a very long formula. I had a few problems with copy/paste. If Excel TRUNCATES it when you paste, try this work-around:
Temporarily re-name the Brackers&Fuses sheet xx. Now paste the following, and then change it back.
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
As you can see this should be bringing line 21. see picture of the table line.
data line.PNG
I will thought using a different cell that will show the result and if its 1 then use the word "fuse" will be displayed so that way is not all in one cell.
Like i said i will try and do some debugging to see how its looking up.
I will say thank you so far for your help with this.
Regards
Daniel
Last edited by l3lackdrake; 07-12-2017 at 05:53 PM.
Thank for sorting that out. I tested out the formula with multiple entries and it all work great.
I Then added two more devices at the bottom of the table, first i added the 160 frame size and it work fine then i added the 500 frame size and this is were it stopped and errored.
The table i have shifted everything down so that line 1 is empty which i can use as the fuse.
as soon as i did that it now bring in 1 where it should bring in 13
Hi Daniel- With your new configurations, the frame size and current trip calculations for these criteria aren't compatible.
Frame size (Amps =168) finds 250 in column U (several rows, but not row99).
Current Trip (R.Cur.=135) finds 140 in column V (row99 only one). So MATCH fails.
I'm not an engineer, so I don't really grasp the principles underlying these specs. You need to decide how to proceed.
Ps. Rather than graphics, upload latest workbook. Saves time for both of us.
Thanks for pointing out what it doing any why its not matching up.
Ideally it needs to check the frame size which would be 250 and then check the current trip size 135 look for the next size up in the 250 frame size. if there is no trip then look at the next frame size and recheck current trip size. once a match is found check kA, voltage and phase.
Also note that the lower voltage can be used on a higher unit voltage if it fall with in the kA rating.
I hope this helps a little . Please let me know if its possible to do this either in one cell or using multiply cells. If not then ill will have to re look at my table and my be go down the root of VB coding.
1) Changed Order of Calculation to match columns on Sheet xx ('Brackers&Fuses').
Phase, Voltage, Frame Size & Current Trip, kA Rating RMS
2) Changed method of determining minimums- shorter + more flexible (SMALL(xx!T3:T102,COUNTIF(xx!T3:T102,"<"&E39)+1)=xx!T3:T102) became (MIN(IF(xx!T3:T102>=E39,xx!T3:T102))=xx!T3:T102)
3) Merged tests for Frame Size & Current Trip. (MIN(IF((xx!U3:U102>=E43)*(xx!V3:V102>=E41),xx!U3:U102+xx!V3:V102/65536))=xx!U3:U102+xx!V3:V102/65536)
This adds a fraction of CTrip to Frame Rating for all rows that meet BOTH criteria, then keeps those that contain the MIN of that array.
The value 65536 was chosen as a divisor because a) it's bigger than any likely Current and b) computers like binary numbers. (2^16)
Also:
Originally Posted by l3lackdrake
Also note that the lower voltage can be used on a higher unit voltage if it fall with in the kA rating.
I'm not sure what you mean by this. The formula is supposed to return a SINGLE result based on minimum compatible values, correct?
I will have a look at this over the weekend.. got a wedding today ^^..
For the voltage device are designed for voltage insulation so a lower voltage can be used on a higher device within the operation limits this means a 470 can work on the 480 and 690.. only reason it would use a 690 volt bracker is if had a useable kA rating..
Hey Daniel, hope the wedding was fun! Best wishes to the... and all that.
After reflecting on Post #12, I decided to concatenate all numeric values (in order of priority) to yield a single numeric "key" for each row. Then one MIN(IF structure yields the SMALLEST key that meets ALL of the corresponding criteria. This, in conjunction with the remaining criteria (Phase as text), represents the optimal solution.
Building the keys entailed formatting each number as text to ensure correct alignment/order. This makes the formula pretty long, but it's actually simpler.
NOTE: While developing this approach, I employed a helper column (xx!M3:M102) to make the keys. The final version doesn't use it, but I left it in the attached workbook so you could see how they work. The "helped" version of the formula - considerably shorter - is also present.
I hope you find this of some use. Happy motoring...-Lee
Thanks for all your help. This is an absolute master piece. Which once i grasp and understand it on a fundamental level ill be able to use on several other application.
I am tho having a little trouble, if i do any changes to the formula even if its to increase the cell range like 103 to 250 is stops working. im using the shift + control + enter but still no luck is there something im missing?
Daniel- Doing it piecemeal like this is just frustrating us both. There's too many moving parts. Let's do this. On Sheet(xx), add all the possible permutations you're going to want to test (as new rows). Then attach a copy of that workbook to a post as you did before. That gives us a STABLE TESTBED. Now when you change the formula and it doesn't work, you can just post a copy of the failed FORMULA, along with parameters, so I can follow what you did. I've enjoyed this challenge, but let's be systematic and finish this. Thanks - Lee
Sorry for all the back forth between us. I will fill this in as much as I can once im at work and write done the process of it..
Like I said this was a working progresses and this step is the first of many which is why I want to get a grip of how it works so I can apply it later on as well.
Again I would like to thank you for your help up till now.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by l3lackdrake
Bookmarks