LinkBack URL
About LinkBacks
Hi,
My question is described in details within the attached WB.
I will appreciate any kind of worksheet formula without helper cells because there is no problem to solve it with a single helper column.
Thanks, Elm
Hi,
My question is described in details within the attached WB.
I will appreciate any kind of worksheet formula without helper cells because there is no problem to solve it with a single helper column.
Thanks, Elm
Last edited by ElmerS; 10-10-2010 at 04:27 AM.
How about this?
Sounds like a big shame at my side because this is the basic INDEX/MATCH combination and foolish enough I was looking for the result at the horizon instead in my backyard.
Thanks a lot,
Elm
Last edited by ElmerS; 10-09-2010 at 07:26 PM.
Hi ElmerS,
Did you ever see that problem where you see 5 cards all Kings, Queens and Jacks. The problem is to pick one and remember it. Then the next screen shows you 5 cards Kings, Queens and Jacks and says "See your card is gone!!!" Amazing? The trick is to show 5 different cards the second time than the first.
I tricked you. I changed your data to make my formula work. You didn't say I couldn't do that in the rules. You simply said no sorting.
You can change this problem back to Unsolved. Why didn't you check to see if my data was the same as yours?
I think the real answer involves Match, Rows and possibly Array Formulas. But the real question is why can't we use all the power Excel has given us to make our lives easier. Just Sort the list or use a helping column.
One for you - Add all the numbers from 1 to 200, don't use Excel or paper, just do it in your head. Ready go! I can do this. Can you? Is it really a problem if you have an answer? Show us the easiest method to accomplish answers so we can all work a little smarter instead of harder.
Re: First runner-up seller
I felt guilty about cheatingso here is the Array Formula to solve the original problem.
You must enter this formula using the Control+Shift+Enter method to get the { and } .
Find the original worksheet with the now correct formula above to test.
Please Login or Register to view this content.
This problem was a real head scratcher. I had to use Array Formulas twice to get the answer.
It took me a few hours as I'm not good with CSE formulas. DonkeyOte and teylyn do these in there sleep, with great ease.
Can anybody come up with an easier and different solution?
My guilt is now mostly gone.I'm sorry, Elmer, for cheating to get the first answer.
Well....
I copied your first [Cheating] formula into my original WB and it works fine.
The only difference between the two formulas is the return of Raleigh, for the Bicycle, opposite Reynolds.
So, where is (if any) the catch no ? or will it not work for a different data table ?
Thanks, Elm
Last edited by ElmerS; 10-10-2010 at 04:31 AM.
FWIW, the "traditional" Array approach would be:
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
I'm surprised anyone bothered to answer, if you cannot take the time to ask the question in the post
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Thanks DO,
If I change B12 to Motorcycle, or any other item (leaving only one single Bicycle at C11) which can be the case and in C16 selecting Bicycle - the formula returns #NUM!
Elm.
You've not outlined (as far as I can tell) what should happen if there is only one item (ie no runner-up) - if you tell us we can help.
You are right.
Your suggested formula returns the last Manufacturer if there is no runner-up.
(See bicycle example - rows 11&12)
So far so good.
Now, let me ask for two additional / different formulas.
1) In the case of no-runner up for bicycle - as mentioned above the formula should return:
our In case which in case of no runner-up will return: "No runner up for that item".
2) If cell B12 will have "Motorcycle" in such case there is no competition because only one manufacturer meets the criteria - and in that case the formula should return: Raleigh
If all the above can be combined into a single formula - this will be excellent.
Thanks, Elm
No it does not. If there is no runner-up it will return a #NUM! error as the SMALL with k of 2 will fail given less than 2 instances of the item exist.Originally Posted by ElmerS
Add a pre-emptive COUNTIF test.
Please Login or Register to view this content.In this case - use a MIN construct for k in the SMALL
Note however the above would still fail if the item did not appear at all...Please Login or Register to view this content.
The two are as I see it mutually exclusive and can not be combined - either no runner up results in "no runner up" output or it results in the winning manufacture it can not do both simultaneously.
If you wish to add a logical flag of some sort to denote which of the above to perform then you could combine all into one formula - ie conduct either formula1 or formula2 based the pre-emptive logical flag.
No more questions.
Thanks a lot.
Elm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks