Re: Maximum Value In Column Of Text & Numbers

This is not as simple as it should be. Finding the max when a 'T' means a value is awkward. I would like to use an Array-Entered formula like this in cell AK132:

([ea]*[/ea])[bf]=IF(MAX(IF(AK36:AK128="T",0.0001,AK36:AK128))=0.0001,"T",MAX(IF(AK36AKH128="T",0.0001,AK36:AK128)))[/bf]

but because AK129 is a merge of 21 cells (2 rows x 7 columns) Excel says it's not valid, so a user defined function used in a cell thus (entered normally, *not *array-entered):

[bf]=TheMax(AK36:AQ128)[/bf]

The code for the function below should be pasted into a Standard code module, not a Worksheet code module:

```
Function TheMax(rng As Range)
For Each cll In rng.Cells
If cll.Value = "T" Then ThisVal = 0.0001 Else ThisVal = cll.Value
If ThisVal > TheMax Then TheMax = ThisVal
Next cll
If TheMax = 0.0001 Then TheMax = "T"
End Function
```

p45cal

ps.Note the average formula in Cell AK129 is probably giving you wrong results (work it out on a calculator and compare). 2 points:

1.Is the rainfall 0 for 28th-31st Jan each day, or is the data simply missing? Empty cells are not used in the Average function, so to include those dates in the average you must enter 0 for each date.

2.Cells with T are also ignored, but I'd have thought you'd want to include them as a day.

Thankfully it's easy to put right (I think): As well as putting 0s in on no-rainfall days, change the formula in cell AK129 from:

[bf]=IF(AK36="","",AVERAGE(AK36:AQ128))[/bf]

to:

[bf]=IF(AK36="","",AVERAGE__A__(AK36:AQ128))[/bf]

or perhaps better:

[bf]=IF(AK36="","",AVERAGEA(AK36:A__K__128))[/bf]p45cal