Looking for formula for lowest costs, excluding zero

[Pipeladylu]

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!!

[KL]

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

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

Regards,
KL


"Pipeladylu" <[email protected]> wrote in message
news:[email protected]...
>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!!

[Pipeladylu]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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!!
>
>
>

[KL]

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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!!
>>
>>
>>

[KL]

Maybe this:

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

Enter as ARRAY formula

Regards,
KL


"KL" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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!!
>>>
>>>
>>>
>
>

[Pipeladylu]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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!!
> >>
> >>
> >>
>
>
>

[Cutter]

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

[Pipeladylu]

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
>
>