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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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

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.

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

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.

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.

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.