Imposing a limit on Min function
 

I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

giblopez,

I'm not sure about how you want to incorporate this into the if statement,
but what about this for your MIN() function:

MIN($BF3:$IV3,D3/2)

....if I am understanding you correctly.

HTH,

Conan




"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

If you can sort the bids in descending order then this should work...
=INDEX($BF3:$IV3,1,MATCH(D3*0.5,$BF3:$IV3,-1))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"giblopez"
wrote in message
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.
Thanks very much.

Imposing a limit on Min function
 

Sorry, I reread your post and realize what I came up with wasn't quite
right.


maybe something like this:

=SMALL($BF3:$IV3,COUNTIF($BF3:$IV3,"<" & $D3/2)+1)



HTH,

Conan




"Conan Kelly" wrote in message
...
giblopez,

I'm not sure about how you want to incorporate this into the if statement,
but what about this for your MIN() function:

MIN($BF3:$IV3,D3/2)

...if I am understanding you correctly.

HTH,

Conan




"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

You could try:

=MAX(MIN($BF3:$IV3),D3/2)

"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

Ignore my last post


"Tyro" wrote in message
. ..
You could try:

=MAX(MIN($BF3:$IV3),D3/2)

"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

"Tyro" wrote...
You could try:

=MAX(MIN($BF3:$IV3),D3/2)
....

If none of the bids met the D3/2 threshold, this would give a
misleading result.

Imposing a limit on Min function
 

Hey All,

Thanks for the prompt replies, I went with RD's for now but some of the
other posts look promising as well. Again, thanks.

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

"Jim Cone" wrote...
If you can sort the bids in descending order then this should
work...
=INDEX($BF3:$IV3,1,MATCH(D3*0.5,$BF3:$IV3,-1))
....

Or with such a sorted range you could use a much shorter formula.

=-LOOKUP(-D3/2,-$BF3:$IV3)

or you could leave the sorting to a function and use

=LARGE($BF3:$IV3,COUNTIF($BF3:$IV3,"="&D3/2))

as a variation on the SMALL approach others showed.

Imposing a limit on Min function
 

Encountering a small problem with this solution. I have some bids where
there is only one bid submitted, which is below the feasible amount (a 66%
savings), and when my formula goes to reject it, it delivers #N/A. is there
some way to return a zero instead?

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

Hi,

This array formula will work.

=MIN(ABS(BF3:IV3-(D3/2)))+(D3/2)

Commit with Ctrl+Shift+Enter and not just Enter.

HTH
Martin


"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

giblopez,

This is kinda convoluted but it should work:

=if(isna(SMALL(BF3:IV3,COUNTIF(BF3:IV3,"<"&D3*0.5) +1)),0,IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTI F(BF3:IV3,"<"&D3*0.5)+1)))

I think that is right...haven't tested it.

HTH,

Conan




"giblopez" wrote in message
...

Encountering a small problem with this solution. I have some bids where
there is only one bid submitted, which is below the feasible amount (a 66%
savings), and when my formula goes to reject it, it delivers #N/A. is
there
some way to return a zero instead?

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above
a
minimum of 50% of the current price (since presumably anything less
isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

What formula are you using that returns an #N/A error?

The formula I suggested will error out with #NUM!

Anyway, will a zero work for both error traps:

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),0,SMALL(BF3:IV3,COUNTIF(BF3:IV3,"
<"&D3*0.5)+1))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"giblopez" wrote in message
...

Encountering a small problem with this solution. I have some bids where
there is only one bid submitted, which is below the feasible amount (a 66%
savings), and when my formula goes to reject it, it delivers #N/A. is
there
some way to return a zero instead?

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid
above a
minimum of 50% of the current price (since presumably anything less
isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of
the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

Ignore this, It falls down if no bid reaches 50%.
I missed that part.

Regards
Martin


"MartinW" wrote in message
...
Hi,

This array formula will work.

=MIN(ABS(BF3:IV3-(D3/2)))+(D3/2)

Commit with Ctrl+Shift+Enter and not just Enter.

HTH
Martin


"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid above a
minimum of 50% of the current price (since presumably anything less isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

Thanks, I think the problem is licked. You're right, before it errored #NUM,
my mistake. The only difference I made to your formula was to change my
result to a blank instead of a zero

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),"",SMALL(BF3:IV3,COUNTIF(BF3:IV3,"<"&D3*0.5)+1) )

Thanks so much for the help!
G

"Ragdyer" wrote:

What formula are you using that returns an #N/A error?

The formula I suggested will error out with #NUM!

Anyway, will a zero work for both error traps:

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),0,SMALL(BF3:IV3,COUNTIF(BF3:IV3,"
<"&D3*0.5)+1))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"giblopez" wrote in message
...

Encountering a small problem with this solution. I have some bids where
there is only one bid submitted, which is below the feasible amount (a 66%
savings), and when my formula goes to reject it, it delivers #N/A. is
there
some way to return a zero instead?

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid
above a
minimum of 50% of the current price (since presumably anything less
isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of
the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

"Ragdyer" wrote...
....
Anyway, will a zero work for both error traps:

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),0,
SMALL(BF3:IV3,COUNTIF(BF3:IV3,"<"&D3*0.5)+1))
....

You don't need to calculate the minimum twice. If MIN(...) were 0,
MIN(...) would also be < D3/2 if D3 0. Since D3 should be positive,
better to make the test OR(D3<=0,MIN(BF3:IV3)<D3/2).

However, this is a whole lot of unnecessary calculation for something
that should be solved with a simple array formula.

=MIN(IF(BF3:IV3=D3/2,BF3:IV3))

If there are values in BF3:IV3 = D3/2, this would return the smallest
of them; otherwise, it'll return 0. And it's much clearer in addition
to being much more efficient and much shorter.

And, FTHOI, returning the LARGEST value in BF3:IV3 if there were no
values in BF3:IV3 = D3/2 could be done using

=LARGE(BF3:IV3,MAX(COUNTIF(BF3:IV3,"="&D3/2),1))

Imposing a limit on Min function
 

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"giblopez" wrote in message
...
Thanks, I think the problem is licked. You're right, before it errored
#NUM,
my mistake. The only difference I made to your formula was to change my
result to a blank instead of a zero

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),"",SMALL(BF3:IV3,COUNTIF(BF3:IV3,"<"&D3*0.5)+1) )

Thanks so much for the help!
G

"Ragdyer" wrote:

What formula are you using that returns an #N/A error?

The formula I suggested will error out with #NUM!

Anyway, will a zero work for both error traps:

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),0,SMALL(BF3:IV3,COUNTIF(BF3:IV3,"
<"&D3*0.5)+1))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"giblopez" wrote in message
...

Encountering a small problem with this solution. I have some bids where
there is only one bid submitted, which is below the feasible amount (a
66%
savings), and when my formula goes to reject it, it delivers #N/A. is
there
some way to return a zero instead?

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid
above a
minimum of 50% of the current price (since presumably anything less
isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of
the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

You'll probably do better if you use Harlan's nice, short suggestion!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"giblopez" wrote in message
...
Thanks, I think the problem is licked. You're right, before it errored
#NUM,
my mistake. The only difference I made to your formula was to change my
result to a blank instead of a zero

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),"",SMALL(BF3:IV3,COUNTIF(BF3:IV3,"<"&D3*0.5)+1) )

Thanks so much for the help!
G

"Ragdyer" wrote:

What formula are you using that returns an #N/A error?

The formula I suggested will error out with #NUM!

Anyway, will a zero work for both error traps:

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),0,SMALL(BF3:IV3,COUNTIF(BF3:IV3,"
<"&D3*0.5)+1))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"giblopez" wrote in message
...

Encountering a small problem with this solution. I have some bids where
there is only one bid submitted, which is below the feasible amount (a
66%
savings), and when my formula goes to reject it, it delivers #N/A. is
there
some way to return a zero instead?

G

"Ragdyer" wrote:

Does this work for you:

=IF(MIN($BF3:$IV3)=0,"",SMALL(BF3:IV3,COUNTIF(BF3: IV3,"<"&D3*0.5)+1))

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"giblopez" wrote in message
...
I have a variety of bids and am attempting to pick the lowest bid
above a
minimum of 50% of the current price (since presumably anything less
isn't
feasible).

Currently I have this function:
=IF(MIN($BF3:$IV3)=0,"",MIN($BF3:$IV3))
and would like to make the last value be the minimum down to 50% of
the
price in cell D3.

Thanks very much.

Imposing a limit on Min function
 

=MIN(IF(BF3:IV3=D3/2,BF3:IV3))

As always Harlan, concise and to the point.<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Harlan Grove" wrote in message
...
"Ragdyer" wrote...
....
Anyway, will a zero work for both error traps:

=IF(OR(MIN($BF3:$IV3)=0,MIN(BF3:IV3)<D3/2),0,
SMALL(BF3:IV3,COUNTIF(BF3:IV3,"<"&D3*0.5)+1))
....

You don't need to calculate the minimum twice. If MIN(...) were 0,
MIN(...) would also be < D3/2 if D3 0. Since D3 should be positive,
better to make the test OR(D3<=0,MIN(BF3:IV3)<D3/2).

However, this is a whole lot of unnecessary calculation for something
that should be solved with a simple array formula.

=MIN(IF(BF3:IV3=D3/2,BF3:IV3))

If there are values in BF3:IV3 = D3/2, this would return the smallest
of them; otherwise, it'll return 0. And it's much clearer in addition
to being much more efficient and much shorter.

And, FTHOI, returning the LARGEST value in BF3:IV3 if there were no
values in BF3:IV3 = D3/2 could be done using

=LARGE(BF3:IV3,MAX(COUNTIF(BF3:IV3,"="&D3/2),1))