The formula to find the smallest number in a row not equal to zero
 

I want create a worksheet which will locate the smallest number in a given
range but I don't want it to locate any cells which equal zero. (Some cells
make be blank)

Try in say B1, array-entered (press CTRL+SHIFT+ENTER):
=MIN(IF(A1:A10<0,A1:A10))
where A1:A10 is the range which may contain zeros
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"seastheday" wrote in message
...
I want create a worksheet which will locate the smallest number in a given
range but I don't want it to locate any cells which equal zero. (Some
cells
make be blank)

Or if the target range is in row1,
you could try in say, A2: =MIN(IF(1:1<0,1:1))
(Array-entered as before)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

What if I am selecting specific cells in a row, (A1,D1,G1...) and not a
RANGE. I am trying to create a column with the lowest price for each item in
a given row. This worksheet will have many ROWS.

"Max" wrote:

Try in say B1, array-entered (press CTRL+SHIFT+ENTER):
=MIN(IF(A1:A10<0,A1:A10))
where A1:A10 is the range which may contain zeros
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"seastheday" wrote in message
...
I want create a worksheet which will locate the smallest number in a given
range but I don't want it to locate any cells which equal zero. (Some
cells
make be blank)

Not really sure what's your set-up
(maybe you could post a sample in plain text?)

but here's another guess ..

Let's say you have
In Sheet1, in A2:E4
---------------

1111 3 0 1 7
2222 3 7 0 4
3333 0 8 5 9
etc

where 1111, 2222 etc in col A are product #s

In Sheet2
-------------------
Listed down in A2:A4 are the product #s

3333
2222
1111
etc

Put in B2:

=MIN(IF(Sheet1!$A$2:$A$100=A2,IF(Sheet1!$B$2:$E$10 00,Sheet1!$B$2:$E$100)))

Array-enter the formula (press CTRL+SHIFT+ENTER)

Copy B2 down to B4

You'll get the min non-zero values for each product in col B, viz:

3333 5
2222 3
1111 1

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"seastheday" wrote in message
...
What if I am selecting specific cells in a row, (A1,D1,G1...) and not a
RANGE. I am trying to create a column with the lowest price for each item
in
a given row. This worksheet will have many ROWS.

Here is a sample of what the sheet looks like. I want to write a formula in
the best bid column which will look at the NET price from each of the
companies listed. Occassionally some companies may not bid on an item and
there NET price would be zero.

Company A Company B
BEST BID
List Discount NET List Discount NET
NET
Price Price Price Price
Price
1/2" Pipe 1.00 .02 .98 1.05 .05 1.00 .98
3/4" Pipe 1.25 .02 1.23 1.25 .05 1.20 1.20
1" Pipe 2.10 .02 2.08 2.17 .05 2.15 2.08

THANKS

"Max" wrote:

Not really sure what's your set-up
(maybe you could post a sample in plain text?)

but here's another guess ..

Let's say you have
In Sheet1, in A2:E4
---------------

1111 3 0 1 7
2222 3 7 0 4
3333 0 8 5 9
etc

where 1111, 2222 etc in col A are product #s

In Sheet2
-------------------
Listed down in A2:A4 are the product #s

3333
2222
1111
etc

Put in B2:

=MIN(IF(Sheet1!$A$2:$A$100=A2,IF(Sheet1!$B$2:$E$10 00,Sheet1!$B$2:$E$100)))

Array-enter the formula (press CTRL+SHIFT+ENTER)

Copy B2 down to B4

You'll get the min non-zero values for each product in col B, viz:

3333 5
2222 3
1111 1

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"seastheday" wrote in message
...
What if I am selecting specific cells in a row, (A1,D1,G1...) and not a
RANGE. I am trying to create a column with the lowest price for each item
in
a given row. This worksheet will have many ROWS.

seastheday wrote:
: Here is a sample of what the sheet looks like. I want to write a formula in
: the best bid column which will look at the NET price from each of the
: companies listed. Occassionally some companies may not bid on an item and
: there NET price would be zero.

: Company A Company B
: BEST BID
: List Discount NET List Discount NET
: NET
: Price Price Price Price
: Price
: 1/2" Pipe 1.00 .02 .98 1.05 .05 1.00 .98
: 3/4" Pipe 1.25 .02 1.23 1.25 .05 1.20 1.20
: 1" Pipe 2.10 .02 2.08 2.17 .05 2.15 2.08

: THANKS

<snip:

Try this:

Assume Column E contains the net prices for Company A and Column H contains the net prices for Company B.
The following will find the lowest price:

(Assumes the data starts in Row 26)

Enter the following in I26 and just copy it down the rows.

=MIN(E26,H26)

If you wish to identify the low bidder add the following in J26 and copy it down:

=IF(I26=E26,"Company A","Company B")

This assumes you have only two companies. If you have more than two bidders
then you can add the net price to the min function and you will need to modify
the company identification a little more, but it can be done.

This produces the following for you example data:

Low bid Company
Data you supplied 0.98 Company A
etc 1.2 Company B
2.08 Company A


Pieter Vandenberg

Another way to try ..

Assuming data below is in A5:H7,
where BestBid is to be calculated in H5 down

----------------------------------------------------------------------------
--BestBid
1/2" Pipe 1.00 .02 .98 1.05 .05 1.00
..98
3/4" Pipe 1.25 .02 1.23 1.25 .05 1.20
1.20
1" Pipe 2.10 .02 2.08 2.17 .05 2.15
2.08

Put in H5, and array-enter:

=MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G50,B5:G5)))

Copy H5 down

This will return the required min NET price figures which are non zeros

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"seastheday" wrote in message
...
Here is a sample of what the sheet looks like. I want to write a formula
in
the best bid column which will look at the NET price from each of the
companies listed. Occassionally some companies may not bid on an item and
there NET price would be zero.

Company A Company B
BEST BID
List Discount NET List Discount NET
NET
Price Price Price Price
Price
1/2" Pipe 1.00 .02 .98 1.05 .05 1.00
..98
3/4" Pipe 1.25 .02 1.23 1.25 .05 1.20
1.20
1" Pipe 2.10 .02 2.08 2.17 .05 2.15
2.08

THANKS

ugh, sorry for the table wrap,
here's another (better?) paste of the sample table
assumed in A4:H7 (labels in B4:H4):

List Discount NET List Discount NET BEST BID
1/2" Pipe 1 0.02 0.98 1.05 0.05 0.7 0.7
3/4" Pipe 1.25 0.02 1.23 1.25 0.05 1.2 1.2
1" Pipe 2.1 0.02 2.08 2.17 0.05 2.15 2.08

NET prices are in cols D and G,
"Best Bid" is in rightmost col, col H
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----