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

[TheEnergyMan]

[TABLE="width: 331"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Occupancy[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Occupancy Start[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Occupancy End[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Occupancy Start Weekend[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Occupancy End Weekend[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DHW[/TD]
[TD]DHW[/TD]
[/TR]
[TR]
[TD]DHW Start Monday[/TD]
[TD]05:00[/TD]
[/TR]
[TR]
[TD]DHW Start Tues - Fri[/TD]
[TD]05:30[/TD]
[/TR]
[TR]
[TD]DHW Stop Mon - Thurs[/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]DHW Stop Fri[/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]DHW Start Sat[/TD]
[TD]09:00[/TD]
[/TR]
[TR]
[TD]DHW Stop Sat[/TD]
[TD]13:00[/TD]
[/TR]
[TR]
[TD]DHW Start Sun[/TD]
[TD]00:00[/TD]
[/TR]
[TR]
[TD]DHW Stop Sun[/TD]
[TD]00:00[/TD]
[/TR]
[TR]
[TD]Cylinder Temps (°C)[/TD]
[TD]61.0°C
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[steve the fish]

See if this works:

=MAX(IF(NOT(ISNUMBER(SEARCH("sat",A1:A609))+ISNUMBER(SEARCH("sun",A1:A609))),B1:B609))

 

[Special-K99]

=MAX(IF(NOT((ISNUMBER(SEARCH("Sat",A7:A609))+ISNUMBER(SEARCH("Sun",A7:A609))+ISNUMBER(SEARCH("C)",A7:A609)))),B7:B609))
Array formula, use Ctrl-Shift-Enter

Searches for "C)" to identify temperature

 

[TheEnergyMan]

That works but when I Add =MAX(IF(AND($A$7:$A$609 < 1,NOT(ISNUMBER(SEARCH("sat",A7:A609))+ISNUMBER(SEARCH("sun",A7:A609)))),B7:B609))

to it, it returns 0, have I Added the 'And' function correctly?

Thanks

 

[AlanY]

this work with a helper column

Book1
	A	B	C
1	Occupancy
2	Occupancy Start
3	Occupancy End	15:00
4	Occupancy Start Weekend
5	Occupancy End Weekend
6	DHW	DHW
7	DHW Start Monday	05:00	FALSE
8	DHW Start Tues - Fri	05:30	FALSE
9	DHW Stop Mon - Thurs	15:00	FALSE
10	DHW Stop Fri	15:00	FALSE
11	DHW Start Sat	09:00	TRUE
12	DHW Stop Sat	18:00	TRUE
13	DHW Start Sun	00:00	FALSE
14	DHW Stop Sun	00:00	FALSE
15	Cylinder Temps (C)	61.0C
Sheet3

Cell Formulas
Range	Formula
B3	{=MAX(IF(C7:C14=FALSE,B7:B14))}
C7	{=ISNUMBER(SEARCH({"Sat","Sun"},A7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Try...

=MAX(IF(B7:B610<1,IF(ISERROR(SEARCH("Sat",A7:A610)),IF(ISERROR(SEARCH("Sun",A7:A610)),B7:B610))))
Ctrl+Shift+Enter

M.

 

[steve the fish]

You cant use AND in an array formula. It doesnt produce an array of values so cant be used. For OR use addition, or for AND use multiplication.

 

[Special-K99]

=MAX(IF((A7:A609>" ")*NOT((ISNUMBER(SEARCH("Sat",A7:A609))+ISNUMBER(SEARCH("Sun",A7:A609))+ISNUMBER(SEARCH("C)",A7:A609)))),B7:B609))
Array formula, use Ctrl-Shift-Enter

What have you got in column A that you havent indicated in your example data?

 

[TheEnergyMan]

=MAX(IF((A7:A609>" ")*NOT((ISNUMBER(SEARCH("Sat",A7:A609))+ISNUMBER(SEARCH("Sun",A7:A609))+ISNUMBER(SEARCH("C)",A7:A609)))),B7:B609))
Array formula, use Ctrl-Shift-Enter

What have you got in column A that you havent indicated in your example data?

sorry, Im assuming you mean Row A, This is what I have (not sure how to table this
[TABLE="width: 331"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Term[/TD]
[TD]Calderglen[/TD]
[/TR]
[TR]
[TD]Occupancy[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Occupancy Start[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Occupancy End[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Occupancy Start Weekend[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Occupancy End Weekend[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DHW[/TD]
[TD]DHW[/TD]
[/TR]
[TR]
[TD]DHW Start Monday[/TD]
[TD]05:00[/TD]
[/TR]
[TR]
[TD]DHW Start Tues - Fri[/TD]
[TD]05:30[/TD]
[/TR]
[TR]
[TD]DHW Stop Mon - Thurs[/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]DHW Stop Fri[/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]DHW Start Sat[/TD]
[TD]09:00[/TD]
[/TR]
[TR]
[TD]DHW Stop Sat[/TD]
[TD]13:00[/TD]
[/TR]
[TR]
[TD]DHW Start Sun[/TD]
[TD]00:00[/TD]
[/TR]
[TR]
[TD]DHW Stop Sun[/TD]
[TD]00:00[/TD]
[/TR]
[TR]
[TD]Cylinder Temps (°C)[/TD]
[TD]61.0°C[/TD]
[/TR]
</tbody>[/TABLE]

My top row are the different locations, then I have Different facilities for each one here shows DHW

 

[TheEnergyMan]

HI all that were Kind enough to reply, I seem to have mixed some of your reccomendations together to get something that works, it isnt that pretty but it works :')

=MAX(IF((A7:A609>" ")*NOT((ISNUMBER(SEARCH("Sat",A7:A609))+ISNUMBER(SEARCH("Sun",A7:A609))+ISNUMBER(SEARCH("Intercept",A7:A609))+ISNUMBER(SEARCH("Set",A7:A609))+ISNUMBER(SEARCH("Enable",A7:A609))+ISNUMBER(SEARCH("Setpoint",A7:A609))+ISNUMBER(SEARCH("Temp",A7:A609))+ISNUMBER(SEARCH("C)",A7:A609)))),B7:B609))

I did not know that i couldnt use the 'And' function, so thanks for letting me know that, I didnt know that i could use the '+' to include different conditions