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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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$100>0,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 ..
 
: 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
Click to expand...
2.08

Put in H5, and array-enter:

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

Copy H5 down

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

Adapt to suit ..
 
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
 
Back
Top