# Return Highest value if Range of cells dont contain "Sat" or "Sun"

#### TheEnergyMan

##### New Member
 Occupancy Occupancy Start Occupancy End Occupancy Start Weekend Occupancy End Weekend DHW DHW DHW Start Monday 05:00 DHW Start Tues - Fri 05:30 DHW Stop Mon - Thurs 15:00 DHW Stop Fri 15:00 DHW Start Sat 09:00 DHW Stop Sat 13:00 DHW Start Sun 00:00 DHW Stop Sun 00:00 Cylinder Temps (°C) 61.0°C

<colgroup><col><col></colgroup><tbody>
</tbody>

So, see the above in excel, 2 columns 16 rows

I am trying to use the function in Cell B3 :
{=MAX(IF(AND(B7:B609 <> 0, \$A\$7:\$A\$609 <> ISNUMBER(SEARCH("Sat",\$A\$7:\$A\$609)), \$A\$7:\$A\$609 <> ISNUMBER(SEARCH("Sun", \$A7:\$A609))), B7:B609)) }

To find the max value (latest time) in the range that is < 1 (so that the Temp value isn't included) and only use the cells that do not contain Sat or Sun

the formula returns 0 ??

when i Use the formula : =MAX(IF(B7:B610 < 1,B7:B610))

It works Fine but i dont want the Sat and Sunday values in there only the weekly values

thanks in advance for any help

#### Special-K99

##### Well-known Member
There is no row A.

Rows go horizontal, coloumns are vertical (like the columns of a building).

Why are you checking column A if it just contains descriptions?

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Replies
8
Views
502
Replies
11
Views
824
Replies
0
Views
277
Replies
10
Views
397

1,132,794
Messages
5,655,339
Members
418,190
Latest member
Timex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back