Find MAX value within a subset of a range

I have a spreadsheet that contains multiple columns with prices (I have defined the range as "Sale_Prices"). I can determine the lowest price in the Sale_Prices range using the MIN function. I now want to find the highest price in the spreadsheet that is no more than $0.99 higher than the lowest price (essentially, I will need to group the prices into $1 ranges). Any help in writing that formula would be appreciated. I would prefer to avoid array formulas if possible.

A sample spreadsheet is attached. The data is in the "Raw Data" tab and the formula should go in cell B4 of the "Calcs" tab. Thanks!

Do you have the MAXIFS function in your version of Excel? (Sorry, I'm not familiar with Excel for Mac versions). If so, you could use that instead of an array formula which would use MAX(IF( …

Hope this helps.

Pete

Yes, I do have the MAXIFS function. I tried this formula:

=MAXIFS(Sale_Prices, Sale_Prices,"<="&B3)

but it returns a#VALUE! error. Maybe it doesn't like the fact that the MAX range and the criteria range are the same. Any thoughts? Thanks.

Thanks, but I need it to look across columns A, C, E and G, not columns A and B. And I don't want to define specific range in these columns, because the spreadsheet will be a template into which other people will copy data, and number of rows used will vary.

It's not the most elegant solution, but the following seems to work:

=MAX(MAXIFS('Raw Data'!A:A, 'Raw Data'!A:A,"<="&B3),MAXIFS('Raw Data'!C:C, 'Raw Data'!C:C,"<="&B3),MAXIFS('Raw Data'!E:E, 'Raw Data'!E:E,"<="&B3),MAXIFS('Raw Data'!G:G, 'Raw Data'!G:G,"<="&B3))

Thanks for the help, Pete_UK and AliGW!