Imposing a limit on Min function

G

giblopez

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

Ragdyer

Does this work for you:

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

Conan Kelly

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
 
J

Jim Cone

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

Conan Kelly

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
 
G

giblopez

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
 
H

Harlan Grove

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

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

giblopez

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
 
M

MartinW

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
 
C

Conan Kelly

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,COUNTIF(BF3:IV3,"<"&D3*0.5)+1)))

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

HTH,

Conan
 
R

Ragdyer

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

giblopez

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
 
H

Harlan Grove

Ragdyer said:
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))
Click to expand...
....

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

RagDyeR

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

Regards,

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

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
 
R

RagDyeR

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

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

Regards,

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

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
 
R

RagDyeR

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

Ragdyer said:
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))
Click to expand...
....

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Min function with an offset 1
MIN function with a variable range 4
MIN Function 2
Conditional formatting on a function 2
Using a MIN, MAX formula on a calculated field in a pivot table 0
How to save Max Min values 1
min function trouble 4
MIN Formula Help 1

Top