I have many columns of different vendors with numbers that sum to a
particular row.
Row/Column: A B C D
1 Vendor1 Vendor2 Vendor3
2 1000 2000 1500
3 500 2000 2000
Totals 1500 4000 3500

"Name of lowest Vendor" "Amount of lowest Vendor"

I want to find the lowest number and display it in a cell and display the
name of the vendor in adjacent cell. What is best way to accomplish this?

Thanks in advance.

I used the MIN formula but it does not ignore zeros. Will an if statement
ignore zeros to find the minimum number in the last row. I think I could use
a VLOOKUP after the minimum number is found.

"evoxfan" wrote:

I have many columns of different vendors with numbers that sum to a
particular row.
Row/Column: A B C D
1 Vendor1 Vendor2 Vendor3
2 1000 2000 1500
3 500 2000 2000
Totals 1500 4000 3500

"Name of lowest Vendor" "Amount of lowest Vendor"

I want to find the lowest number and display it in a cell and display the
name of the vendor in adjacent cell. What is best way to accomplish this?

Thanks in advance.

try something like this:

=IF(SUM(B2:D2)=0,0, SMALL((B2:D2),COUNTIF(B2:D2,0)+1))

the if statement checks to see if everything is 0, and will output 0 if true
Small( looks up the range, rank of number) - so the range is B2:D2, and then
the countif() will set a starting point for it to find the lowest number.
so:
0,0,1,2,3
will ouptut 1, because countif() +1 will return 3. so it will look for the
3rd smallest number. which essentially gets rid of 0's





"evoxfan" wrote:

I used the MIN formula but it does not ignore zeros. Will an if statement
ignore zeros to find the minimum number in the last row. I think I could use
a VLOOKUP after the minimum number is found.

"evoxfan" wrote:

I have many columns of different vendors with numbers that sum to a
particular row.
Row/Column: A B C D
1 Vendor1 Vendor2 Vendor3
2 1000 2000 1500
3 500 2000 2000
Totals 1500 4000 3500

"Name of lowest Vendor" "Amount of lowest Vendor"

I want to find the lowest number and display it in a cell and display the
name of the vendor in adjacent cell. What is best way to accomplish this?

Thanks in advance.

for looking up the vendors, just use LOOKUP. i find that V or HLOOKUP doesnt
really make anything simpler.

Lookup(Value, Array, Return Array)
so.. if Amount of lowest vendor is in B4,
B4=IF(SUM(B2:D2)=0,0, SMALL((B2:D2),COUNTIF(B2:D2,0)+1))
and Lowest vendor name is in B5
B5=Lookup(B4, B2:D2, B1:D1) should work

The question now is: what happens if a vendor sells nothing?


"Derrick" wrote:

try something like this:

=IF(SUM(B2:D2)=0,0, SMALL((B2:D2),COUNTIF(B2:D2,0)+1))

the if statement checks to see if everything is 0, and will output 0 if true
Small( looks up the range, rank of number) - so the range is B2:D2, and then
the countif() will set a starting point for it to find the lowest number.
so:
0,0,1,2,3
will ouptut 1, because countif() +1 will return 3. so it will look for the
3rd smallest number. which essentially gets rid of 0's





"evoxfan" wrote:

I used the MIN formula but it does not ignore zeros. Will an if statement
ignore zeros to find the minimum number in the last row. I think I could use
a VLOOKUP after the minimum number is found.

"evoxfan" wrote:

I have many columns of different vendors with numbers that sum to a
particular row.
Row/Column: A B C D
1 Vendor1 Vendor2 Vendor3
2 1000 2000 1500
3 500 2000 2000
Totals 1500 4000 3500

"Name of lowest Vendor" "Amount of lowest Vendor"

I want to find the lowest number and display it in a cell and display the
name of the vendor in adjacent cell. What is best way to accomplish this?

Thanks in advance.

Perhaps:
191(click to view image)
The formulae in red are just textual representations of the formulae in
the cells to the right. The upper one should be array-entered (ie.
commited with the key combination of Shft+Ctrl+Enter which will show the
curly braces).
Those formulae in copyable text:
=MIN(IF(B4:D40,B4:D4))
=INDEX(B1:D1,MATCH(B7,B4:D4,0))

but what happens if there are 2 or more vendors, all with the lowest
value - I think only the first will be shown.


+-------------------------------------------------------------------+
|Filename: 2009-07-24_192123.jpg |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=191|
+-------------------------------------------------------------------+

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119304