Looking for formula for lowest costs, excluding zero
 

I found a formula for determining the lowest costs from several columns, but
I cannot get it to work in my worksheet. This is nearly 1000 lines long. I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=MIN(IF(A1:D1000,A1:D1000))

Regards,
KL


"Pipeladylu" wrote in message
...
I found a formula for determining the lowest costs from several columns,
but
I cannot get it to work in my worksheet. This is nearly 1000 lines long.
I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

I tried the formula with the array, but it still did not work. The formula
that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5)))
Do you think this is correct?

"KL" wrote:

Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=MIN(IF(A1:D1000,A1:D1000))

Regards,
KL


"Pipeladylu" wrote in message
...
I found a formula for determining the lowest costs from several columns,
but
I cannot get it to work in my worksheet. This is nearly 1000 lines long.
I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

Hi,

Your formula makes no sense to me, can you explain what you are trying to
achieve and which columns you want to evaluate.

REgards,
KL


"Pipeladylu" wrote in message
...
I tried the formula with the array, but it still did not work. The formula
that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5)))
Do you think this is correct?

"KL" wrote:

Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=MIN(IF(A1:D1000,A1:D1000))

Regards,
KL


"Pipeladylu" wrote in message
...
I found a formula for determining the lowest costs from several columns,
but
I cannot get it to work in my worksheet. This is nearly 1000 lines
long.
I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

Maybe this:

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

Enter as ARRAY formula

Regards,
KL


"KL" wrote in message
...
Hi,

Your formula makes no sense to me, can you explain what you are trying to
achieve and which columns you want to evaluate.

REgards,
KL


"Pipeladylu" wrote in message
...
I tried the formula with the array, but it still did not work. The
formula
that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5)))
Do you think this is correct?

"KL" wrote:

Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=MIN(IF(A1:D1000,A1:D1000))

Regards,
KL


"Pipeladylu" wrote in message
...
I found a formula for determining the lowest costs from several
columns,
but
I cannot get it to work in my worksheet. This is nearly 1000 lines
long.
I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

I have a large quote that I am working on and have pricing from several
vendors. I am nearly complete and would like to create a formula to
determine the lowest cost per line. The costs appear in lines H, L, T & U.
I don't want zero to become a part of the lowest pricing. Thanks KL!!!

"KL" wrote:

Hi,

Your formula makes no sense to me, can you explain what you are trying to
achieve and which columns you want to evaluate.

REgards,
KL


"Pipeladylu" wrote in message
...
I tried the formula with the array, but it still did not work. The formula
that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5)))
Do you think this is correct?

"KL" wrote:

Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=MIN(IF(A1:D1000,A1:D1000))

Regards,
KL


"Pipeladylu" wrote in message
...
I found a formula for determining the lowest costs from several columns,
but
I cannot get it to work in my worksheet. This is nearly 1000 lines
long.
I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

If you want to determine only the lowest cost for each column (and not
the number of times the lowest cost appears) then try this:

=IF(MIN(H2:H200)0,SMALL(H2:H200,COUNTIF(H2:H200,0 )+1),MIN(H2:H200))

This example is for column H (range H2:H200) so adjust as necessary and
then copy it to to your other columns.

It is not an array formula so just enter in normal way


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=468906

Thanks... It works great!! I really appreciate your help

"Cutter" wrote:


If you want to determine only the lowest cost for each column (and not
the number of times the lowest cost appears) then try this:

=IF(MIN(H2:H200)0,SMALL(H2:H200,COUNTIF(H2:H200,0 )+1),MIN(H2:H200))

This example is for column H (range H2:H200) so adjust as necessary and
then copy it to to your other columns.

It is not an array formula so just enter in normal way


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=468906