=Max

[yekut1102]

Here is the set up:

Everything happens on a repeating scale of 24 because I'm pulling the average hourly data and need daily statistics. (A1= the data generated from time 00:00; A24=data from 24:00...)

Column A, B, C are connected to a database that pulls hourly average data. They are summed in column D.
Column E = column D's values between 1:6 & 22:24
Column F= column D's values between 7:21

Problem: I need to have a =Max function that will return the highest value thus far in the day without giving me #VALUE!

Anyone care to share some wisdom? Thank you!

 

[Richard Schollar]

Hi

You'll need to provide some sample values as if just numbers, blanks and/or text you shouldn't be getting a #VALUE! with the MAX() function. How are you writing the formula currently that results in #VALUE! ?

 

[yekut1102]

Here is a simplified example:

(hour of day) 00:00 A1=700, B1=800, C1=715
(hour of day) 01:00 A2= 820, B2=715, C2=900
(hour of day) 02:00 A3= No Good Data For Calc, B3= No Good Data For Calc, C3= No Good Data Calc* for this example let's say the time is 1:00 and the 2:00 data is yet to be determined.

D1=sum(A1,B1,C1) .... = 2215
D2=sum(A2,B2,C2) .... = 2435
D3=sum(A3,B3,C3) .... =#VALUE!

E1=Max(D1:D3) and this returns "#VALUE!' instead of 2435 which would be the highest real value...

I hope this example helps...I know it's kind of messy and perhaps not as clear. Nevertheless I really appreciate your help regardless of whether or not this problem is resolved.

Dan

 

[Richard Schollar]

What are the exact values in A3:C3? If the cells literally contain "No good data for calcs" then I would expect your SUM to return 0 rather than #VALUE! in which case your MAX function should return the correct value...

 

[yekut1102]

I'm connected to a database and use ctrl+alt+shift+F9 to update the values in cells A, B, & C as the data is generated every hour (these values are the average hourly electric consumption in kW).

So in the previous example A3:C3 hasn't yet generated a hourly/kW value, so cells A3, B3, C3 return "No Good Data for Calc" and when summed in cell D3 it reads "#VALUE!".

And out of the 3 summation cells:
D1=sum(A1,B1,C1) .... = 2215
D2=sum(A2,B2,C2) .... = 2435
D3=sum(A3,B3,C3) .... =#VALUE!
...#VALUE! is returned in cell E1 when I type =Max(D1:D3)

Thank you for your help Richard!

 

[Richard Schollar]

Do you still get a #VALUE! if D3 contains

=SUM(A3:C3)

instead?

Alternatively, you could write the MAX as:

=MAX(IF(ISNUMBER(D1:D3),D1:D3))

which is an array formula and so must be entered with Ctrl+Shift+Enter - following successful entry, Excel will surround the formula text in the formula bar with {} brackets (do not try and enter these manually yourself).

 

[yekut1102]

=MAX(IF(ISNUMBER(D1:D3),D1:D3)) worked like a charm.


Richard, much thanks! I really appreciate your help in walking me through this!


Best Regards,

Daniel