Find the lowest number?
 

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.

Find the lowest number?
 

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.

Find the lowest number?
 

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.

Find the lowest number?
 

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.

Find the lowest number?
 

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