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

TheEnergyMan

New Member
Joined
Mar 12, 2019
Messages
12
Occupancy
Occupancy Start
Occupancy End
Occupancy Start Weekend
Occupancy End Weekend
DHWDHW
DHW Start Monday05:00
DHW Start Tues - Fri05:30
DHW Stop Mon - Thurs15:00
DHW Stop Fri15:00
DHW Start Sat09:00
DHW Stop Sat13:00
DHW Start Sun00:00
DHW Stop Sun00: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
Joined
Nov 7, 2006
Messages
8,350
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.

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top