PDA

View Full Version : [SOLVED] Find two minumum values from a range



ertan
07-29-2005, 07:18 AM
Hello all,

I need help for a formula or (VBA) solution for following problem ;


In given range need find the minumum 2 values for each product and write to some other column those values as well as belonging Comp (CompA , CompB ect.)

Zero or none values shouldn be taken into consideration.
Attached small sample (Real data around 15 columns wide and several hundreds rows)

Thank you for your help..

Regards

Bob Phillips
07-29-2005, 07:41 AM
Hello all,

I need help for a formula or (VBA) solution for following problem ;


In given range need find the minumum 2 values for each product and write to some other column those values as well as belonging Comp (CompA , CompB ect.)

Zero or none values shouldn be taken into consideration.
Attached small sample (Real data around 15 columns wide and several hundreds rows)


=SMALL(IF(rng<>0,rng),1)

and


=SMALL(IF(rng<>0,rng),2)

which are array formulae, so commit with Ctrl-Shift-Enter
.

ertan
07-29-2005, 08:05 AM
Thank you for your support XLD,

With your help now I am able to find 2 min values , but I also need to find those corresponding Comp (CompA , CompB etc..) so I would know which 2 companies have the smallest figure for a given pruduct.

I hope you understand my second part of the problem.
In any case thank you very much your quick reply.

Best regards

Ertan

Bob Phillips
07-29-2005, 08:21 AM
Thank you for your support XLD,

With your help now I am able to find 2 min values , but I also need to find those corresponding Comp (CompA , CompB etc..) so I would know which 2 companies have the smallest figure for a given pruduct.

I hope you understand my second part of the problem.
In any case thank you very much your quick reply.

This should get you the text for the smallestr


=INDEX(D11:L11,MATCH(SMALL(IF(D13:L13<>0,D13:L13),1),D13:L13,0))

again an array formula

malik641
07-29-2005, 08:28 AM
Hey xld, how come you have to use MATCH with the SMALL function?? Why do I get a reference error when I use this:


{=INDEX(D11:L11,SMALL(IF(D13:L13<>0,D13:L13),1)}

What's the difference?????

Bob Phillips
07-29-2005, 08:35 AM
Hey xld, how come you have to use MATCH with the SMALL function?? Why do I get a reference error when I use this:

{=INDEX(D11:L11,SMALL(IF(D13:L13<>0,D13:L13),1)}

What's the difference?????

Because INDEX is looking for row and column numbers. The MATCH is used to match up the smallest number with the range from which that smallest number is extracted from, the column (in this case, but would be row if the range was columnar).

If you don't use MATCH, you will be using a row/column index of that smallest number, which is likely to be negative, non-integer, or bigger than the range being looked into, all of which will cause a failure.
.

malik641
07-29-2005, 08:35 AM
Wait....Is it because the SMALL function only returns a numeric value and not a cell reference??

malik641
07-29-2005, 08:37 AM
OOOH, okay...
Man I hate posting at the SAME time someone else does....

Bob Phillips
07-29-2005, 08:43 AM
Wait....Is it because the SMALL function only returns a numeric value and not a cell reference??

No, INDEX wants a numeric value.

malik641
07-29-2005, 08:45 AM
okay, I got it:thumb

Thanks xld!

ertan
07-29-2005, 08:55 AM
XLD,

Thank you very much for your fast and valuable answers. That solve the problem.
But of course if problems finishes... dont be suprise if I come of different one tomorrow..:hi:

All the best for you..

Regards

Ertan

Bob Phillips
07-29-2005, 09:01 AM
But of course if problems finishes...

It only means that you are closer to the next one. :)

ertan
07-30-2005, 02:23 AM
Xld and other friends?

Well as I said yesterday here it comes...

XLD, your solution to my problem was excellent for the given conditions. But now I have a challenging different situation. (Actually it was my mistake
Or not foreseeing the real problem). Under each company name I have 4(sometimes 5) different criteria?s. (These are Numbers just representing dates. (7 - 10 - 12 - 14 etc...). What I would like to achieve is:

To find minimum 2 figures related Product - related Company -
related date number...

It is difficult to put it in words (especially if you try with your second language) therefore I attached example file.

Thank you for help
Regards

Ertan

PS: I wasn?t sure it should be a different thread or not that is why I continue here... (And of course this is not only address to XLD who was great help for the previous part)

Bob Phillips
07-30-2005, 06:38 AM
Well as I said yesterday here it comes...

XLD, your solution to my problem was excellent for the given conditions.

But now I have a challenging different situation. (Actually it was my mistake

Or not foreseeing the real problem)

Under each company name I have 4(sometimes 5) different criteria?s. (These are Numbers just representing dates. (7 - 10 - 12 - 14 etc...)

What I would like to achieve is:

To find minimum 2 figures related Product - related Company -
related date number...

It is difficult to put it in words (especially if you try with your second language) therefore I attached example file.

Is this what you want


=INDEX($D8:$S8,MATCH(V$6,$D$6:$S$6,0)+MATCH(V$7,$D$7:$G$7,0)-1)

in cell V8, copy down and across

ertan
08-01-2005, 12:53 AM
XLD,



Thank you for your response to my problem.



Your latest solution finds the correct value if the Company is known.

But the problem is I don?t know which company will be for the 7 days for minimum 2 values!

I think first need to find, which company is the 1st minimum value and which company is the 2nd minimum value for the Product A.

Need repeat this process to find for 10 days ? 12 days ? 14 days..etc.



And do the same for the Product B?.Product N?etc..







Maybe I need change how data is lay out..



I feel that I am not very clear about expressing the problem. If so please let me know I try to be more specific.



Your help appreciated.



Regards



Ertan