A mix of functions ?

[Keith (Southend)G]

I have a function on my spreadsheet that I use in two places to give
a number, what I now want is to then get a return of the maximum value
(I then will want a similar version that returns the minimum value).
These are the two functions I am trying to work with:

e.g.
=VLOOKUP(B2,'Tn05-01_18-06'!$A$1:$B$171,2,FALSE)
and
=VLOOKUP(B2,'Tn05-01_06-18'!$A$1:$B$171,2,FALSE)
(there are subtle differences between the two)

or

=2&(D2<0)+1-1&RIGHT("000"&ABS(D2)*10,3)
and
=2&(F2<0)+1-1&RIGHT("000"&ABS(F2)*10,3)
(again subtle differences)

The problem I have if I use =MAX or =MIN is if one of the entries is
missing it returns #N/A which then that filter through to the final
result.

I hope this is not to confusing.

Many thanks

Keith (Southend)
http://www.southendweather.net

 

[JE McGimpsey]

One way:

The best way is to trap the #N/A's:

=IF(ISNA(MATCH(B2,'Tri-01_18-06'!$A$1:$B$171, FALSE)), "",
VLOOKUP(B2,'Tn05-01_18-06'!$A$1:$B$171,2,FALSE)

MAX() will then treat a "" result as if it were 0.


Else, you can trap the errors:

=IF(COUNT(J2, K3)=2, MAX(J2,K3), "")

 

[Keith (Southend)G]

One way:

The best way is to trap the #N/A's:

   =IF(ISNA(MATCH(B2,'Tri-01_18-06'!$A$1:$B$171, FALSE)), "",
VLOOKUP(B2,'Tn05-01_18-06'!$A$1:$B$171,2,FALSE)

MAX() will then treat a "" result as if it were 0.

Else, you can trap the errors:

   =IF(COUNT(J2, K3)=2, MAX(J2,K3), "")

Perhaps I'm implementing this wrongly.
The first version just sits in the cell with no return (I maybe need
to double check some bits)
The second one is fine until I get one of the entries with #N/A the
the cell is left 'blank'

Many thanks for your help

Keith (Southend)

 

[JE McGimpsey]

First formula:

The returned value is a null string (a 'blank').

You didn't say what you'd like it to be if the searched-for value is not
found - replace the "" with that value.



Second formula. Again, what would you prefer happen if one of the
arguments is not a number?

If you use the first formula, then you can use MAX() and MIN() since
they ignore the null string.

 

[Keith (Southend)G]

First formula:

The returned value is a null string (a 'blank').

You didn't say what you'd like it to be if the searched-for value is not
found - replace the "" with that value.

Second formula. Again, what would you prefer happen if one of the
arguments is not a number?

If you use the first formula, then you can use MAX() and MIN() since
they ignore the null string.

Basically if there is a number in both columns I'm after either the
Max (or Min) of the two.
If only one column has a number, I just want that number.
If missing from both columns, leave blank.

Many thanks

Keith (Southend)

 

[JE McGimpsey]

My recommendation:

in J1:

=IF(ISNA(MATCH(B2,'Tri-01_18-06'!$A$1:$B$171, FALSE)), "",
VLOOKUP(B2,'Tn05-01_18-06'!$A$1:$B$171,2,FALSE)


in J2:

=IF(ISNA(MATCH(B2,'Tri-01_ 06-18'!$A$1:$B$171, FALSE)), "",
VLOOKUP(B2,'Tn05-01_06-18'!$A$1:$B$171,2,FALSE)


in J3:

=IF(COUNT(J1:J2)>0, MAX(J1:J2), "")

 

[Keith (Southend)G]

My recommendation:

in J1:      

   =IF(ISNA(MATCH(B2,'Tri-01_18-06'!$A$1:$B$171, FALSE)), "",
VLOOKUP(B2,'Tn05-01_18-06'!$A$1:$B$171,2,FALSE)

in J2:

   =IF(ISNA(MATCH(B2,'Tri-01_ 06-18'!$A$1:$B$171, FALSE)), "",
VLOOKUP(B2,'Tn05-01_06-18'!$A$1:$B$171,2,FALSE)

in J3:

   =IF(COUNT(J1:J2)>0, MAX(J1:J2), "")

Still no luck on this one, could I possibly email the spreadsheet to
you, it may make things clearer?

If you email me on kreh <at> southendweather <dot> net I can reply
with it attached.

If not, thanks for help to date.

Keith (Southend)

 

[Keith (Southend)G]

Still no luck on this one, could I possibly email the spreadsheet to
you, it may make things clearer?

If you email me on kreh <at> southendweather <dot> net I can reply
with it attached.

If not, thanks for help to date.

Keith (Southend)

I think I've cracked it using a Macro as follows in conjunction with
some other functions:

Function new_max(m1 As Variant, m2 As Variant)

If m1 = "100" And m2 = "100" Then
new_max = ""
Exit Function
End If

If m1 = "" Then
new_max = m2
Exit Function
End If

If m2 = "" Then
new_max = m1
Exit Function
End If

If m1 = "100" Then
new_max = m2
Exit Function
End If

If m2 = "100" Then
new_max = m1
Exit Function
End If

If Val(m1) > Val(m2) Then
new_max = m1
Else
new_max = m2
End If

If Val(m1) = Val(m2) Then
new_max = m1

End If

End Function

Keith(Southend)