Using the small function to update corresponding values

**virencm** · 02-12-2019, 02:49 PM

Hello,

can someone help with the attached file please?

I have tried to explain it all in the attachment.

Essentially what i need is a formula to find the 1st and second smallest values in a column and return the part names.

Please take a look at the sheet and let me know if you need more information.

Appreciate your help!

Regards
V

**KOKOSEK** · 02-12-2019, 03:27 PM

Try into B31:

Formula:

Please Login or Register  to view this content.

drag it right/down as needed.

**virencm** · 02-12-2019, 03:43 PM

Hi KOKOSEK,

I used the formula in B31 and dragged it down though the column and it returns "D,D" on all cells

Am i doing something wrong?

**Tsjallie** · 02-12-2019, 04:28 PM

Gave it a try and think this is what you're looking.

**KOKOSEK** · 02-12-2019, 05:30 PM

Originally Posted by virencm

Hi KOKOSEK,
I used the formula in B31 and dragged it down though the column and it returns "D,D" on all cells
Am i doing something wrong?

Problem is, for ex. Car1: D/E/F/H/I/J/L/N are the same smallest, so which is smallest, which is second smallest?
Or very similar car 7 (D-K)? Or smallest D-K, second smallest E?
I see now I little bit understand incrorrectly but meantime, Tsjallie's solution looks good (however it's hard to say for col B than order is D/E/F is correct if all of three have the same price, why not FED)

**GeoffW283** · 02-12-2019, 08:26 PM

Another attempt:

Please try the following in B31 entered as an array formula (CTRL-SHIFT-ENTER) then copied down and across:

Formula:

Please Login or Register  to view this content.

I believe this produces the required output.

Sorry for the daunting formula. It was built in several relatively simple steps:
a) Find the smallest value in the column
b) Find the second smallest value in the column discounting duplicates of the smallest value
c) Make an array of column-A with rows set to "" if they contain anything other than the smallest or second smallest values blank
d) Remove the blanks from the (c) array

See the attached workbook.

**GeoffW283** · 02-12-2019, 08:39 PM

Comparing my results with Tsjallie's, we differ, I think, only for Car-6

Tsjallie has F, G, H, D

I have: D, E, F, G, H, I, J, L, N

The smallest values are 10002 and 12398. Unless I'm misunderstanding something, Tsjallie seems to be missing the duplicates of 12398

Geoff

**KOKOSEK** · 02-12-2019, 09:01 PM

Due to the fact that there are many items with the same value, IMHO first row should contains for ex. D,E,F,etc as items related to smallest value, second row for ex. N,M,X etc. as item for 'seconds' smallest.

EDIT:
I've created something like this:

Formula:

Please Login or Register  to view this content.

drag it right and down.
That is what I was thinking.

**Bo_Ry** · 02-13-2019, 12:03 AM

Please try at B31 drag across and down

Formula:

Please Login or Register  to view this content.

**virencm** · 02-13-2019, 06:37 AM

Thank you all for your solutions on this one.

GeoffW283 and Bo_Ry have provided me the best solution to this problem.

Thanks you.

Cheers
Viren

**GeoffW283** · 02-13-2019, 01:33 PM

Glad to have helped - thanks for the feedback!

**virencm** · 03-01-2019, 09:28 PM

Hi GeoffW283 & Bo_Ry

You guys provided me with the answer to the attached problem i had last time.
I was wondering if you could help if I added another dimension to this spreadsheet.

So the intial problem i had was to find the first smallest and second smallest values and paste corresponding in table below.... This has been solved.

What i now have is a Value that needs to be checked before we add the secondsmallest , 3rd smallest....

I have explained it in the attached spreadsheet. Its a complex one...

Please take a look at the spreadsheet and let me know if you need any further information.

Cheers
Viren

**virencm** · 03-01-2019, 10:12 PM

sorry everyone... just read the rules and i shouldnt be posting a new issue to a old post..

i will create a new post..

Using the small function to update corresponding values

LinkBack

Thread Tools

Rate This Thread

Display

Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Re: Using the small function to update corresponding values

Thread Information

Users Browsing this Thread

Similar Threads

Changing Zero values and adding or subtracting small amounts from values in other cells

How Small Function works when small(array,1),small(array,2) are same ?

[SOLVED] Auto update in a graph which reads from a column which includes a small function

small or min function excluding values in an array

[SOLVED] Use criteria for values in range for small function

What function to select the last 3 small values from a list ?

[SOLVED] What function to select the last 3 small values from a list ?

Bookmarks

Bookmarks

Posting Permissions