# Find highest and lowest number less than and greater than a value

#### westend9876

##### Member
I'm stumped. I need equations to solve the problem in Rows 9 and 20. The correct answers are in Rows 10 and 21. I prefer a result that does not require an array if possible. The sample excel file is also attached. Thanks for the help.

#### Attachments

• 8.6 KB Views: 13

#### pecoflyer

##### Active Member
MAXIF and MINIF functions?

#### westend9876

##### Member
MAXIF and MINIF functions?
I don't believe I have MAXIF and MINIF functions in my version of excel. They did not pop up as a selection when typing in the formula bar.

#### bosco_yip

##### Excel Ninja
Try,

[C3] : =AGGREGATE(14,6,A1:A7/(A1:A7<=5),1)

[C14] : =AGGREGATE(15,6,A12:A18/(A12:A18>=5),1)

#### pecoflyer

##### Active Member
I don't believe I have MAXIF and MINIF functions in my version of excel. They did not pop up as a selection when typing in the formula bar.
Yes, this is one of the points that could be improved on the forum, namely showing the XL version in the profile attached to each post.

#### GraH - Guido

##### Well-Known Member
@pecoflyer , you are spot on and the forum platform allows this.
@Hui , @r1c1 would that not be something to consider? I think knowing the version of excel and the operating system it is on, really helps to offer working solutions considering the OP's context.

#### pecoflyer

##### Active Member
Thank you. This works perfectly.
And have you understood how it works? AGGREGATE is tricky to use, but very useful