Maximum Smaller

[silvernight84]

Hi there, i'm new, sorry for my bad English...
My problem is this:
I would like to write a formula to have a result in a cell that gives me the maximum value of a column, but smaller than another cell.

For example:
C1 = Maximum value in column A1:A20, but smaller than B1.

Thank you in advance for the help, i hope you understand my question.

[zbor]

This?

=MIN(MAX(A1:A20);B1)

[silvernight84]

no... it gives me the value in B1...

[DonkeyOte]

I think you're saying you want something like

C1: =MAX(IF(A1:A20<B1,A1:A20))
committed with CTRL + SHIFT + ENTER

Alternatively

C1: =SMALL(A1:A20,COUNTIF(A1:A20,"<"&B1))

(alter comma delimiter according to regional settings if/as required)

[martindwilson]

=MAX((A1:A20<B1)*(A1:A20))
confirmed with CSE

[zbor]

yes.. Only MAX instead of MIN in first case

[silvernight84]

I think you're saying you want something like

C1: =MAX(IF(A1:A20<B1,A1:A20))
committed with CTRL + SHIFT + ENTER

Alternatively

C1: =SMALL(A1:A20,COUNTIF(A1:A20,"<"&B1))

(alter comma delimiter according to regional settings if/as required)

Sorry, they don't work, but we're near it.
your second formula gave me the minumum bigger than b1. In other words, the opposite.

[silvernight84]

=BIG(A1:A20;COUNTIF(A1:A20;">"&B1)+1)
This is the right one.
Thank you very much!!!

[zbor]

Did you commited with ctrl+shift+enter?

you need to see brackets {=MAX(IF(A1:A20<B1;A1:A20))} around formula

[martindwilson]

you dont need the IF part for max only for min
BIG ?

[c.vaibhav]

Hi silvernight,

If suppose following is your data

A1 - 1
A2 - 2
A3 - 4
A4 - 5
A5 - 5.5
A6 - 7
A7 - 8
A8 - 8
A9 - 10
A10 - 11

And B1 - 6

Then what value do you want C1 should take.

Should it be 5.5 or something else.

[silvernight84]

With brachets it says error... Don't know why...

[silvernight84]

Hi silvernight,

If suppose following is your data

A1 - 1
A2 - 2
A3 - 4
A4 - 5
A5 - 5.5
A6 - 7
A7 - 8
A8 - 8
A9 - 10
A10 - 11

And B1 - 6

Then what value do you want C1 should take.

Should it be 5.5 or something else.

right, 5.5

[DonkeyOte]

Again...

C1: =SMALL(A1:A20,COUNTIF(A1:A20,"<"&B1))

You would get an error if there were no values in A1:A20 less than B1 so you could encase within an IF

C1: =IF(MIN(A1:A20)>=B1,"",SMALL(A1:A20,COUNTIF(A1:A20,"<"&B1)))

[silvernight84]

Again...

C1: =SMALL(A1:A20,COUNTIF(A1:A20,"<"&B1))

You would get an error if there were no values in A1:A20 less than B1 so you could encase within an IF

C1: =IF(MIN(A1:A20)>=B1,"",SMALL(A1:A20,COUNTIF(A1:A20,"<"&B1)))

Right, these 2 work perfectly, thanks!!!

[zbor]

Make this thread [solved]. thx