Smallest value

**aliyaser** · 08-22-2011, 09:13 AM

Hello there,

i was wondering if someone could help me there.

I have a sheets containg the follwing values.

prefix desc Belgacom 09GSM_G 09GSM_T 42com
1201 USA 0.0057 0.0124 0.0071
1202 USA 0.0057 0.0124 0.0071
1203 USA 0.0057 0.0071 0.0057

now i want to get the following information

Prefix Desc
1201 USA in cel 1 cheapest rate 0.0057 and cell 2 cheapest supplier Belgacom

I can use cheapest supllier by the formula =smallest(A3:A5,1) or 2 or 3
for finding supplier i have formula =INDEX(C1:F1,MATCH(SMALL(C2:E2,1),C2:E2,0)) and i can copy this down.

Butt here comes the problem
prefix 1203 has 2 suppliers with same rate, and with the formula i gett for the first samllest and the second smallest value Belgacom, while it should give me for the second value provider 09GSM_T

Can anyone help me on this please......

**NBVC** · 08-22-2011, 09:21 AM

Assuming your SMALL formula begin at H2 and go to J2 and then down, then in K2 try:

=INDEX($C$1:$E$1,SMALL(IF($C2:$E2=H2,COLUMN($C2:$E2)-COLUMN($C2)+1),COUNTIF($H2:H2,H2)))

confirmed with CTRL+SHIFT+ENTER and not just ENTER and copy across 3 columns and down

**aliyaser** · 08-22-2011, 10:13 AM

i try butt it gives some error.

Maybe beceause of i have mentioned wrong values.

please see attached file, in second sheet i want to know thhe provider of the smalles value.
Maybe you can explain better by the attahed sheet.

**NBVC** · 08-22-2011, 10:26 AM

In D2 enter this formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER keys.

Then copy down.

Then select from D2:D20 and copy, then paste to top of each column afterwards.

See attached.

Note: to remove errors, change the SMALL() formula in C2 to:

=IF(ISERR(SMALL('Prize kopie'!C2:AC2,1)),"",SMALL('Prize kopie'!C2:AC2,1))

and copy down and do similar for the other columns... then the formula I gave in D2 would now be:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copy down, then copy column to other columns as before.

I fixed column C and D for you in the attachment.

Smallest value

LinkBack

Thread Tools

Rate This Thread

Display

Smallest value

Re: Smallest value

Re: Smallest value

Re: Smallest value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions