Find Maximum. Some Values Are Represent By Text

  • I have a column of values the values can be "T", 0.00 ->any number to the nearest hundredth. I thought I had this problems solved a while back but after having time to do some quality control I found that anytime there is a value of "T" the macro does not work. I have had some great help with this in the past to get as far as I have but I still cannot seems to get it 100%. Any help is greatly appreciated. I have attached a sample file. The result that I would like to see is in the cell BH145 is "16,17,23" without the quotes of course.


    When you open the worksheet you will see that the values of concern are precipitation values. These values are rounded to nearest hundredth. When there is a trace of precip however it is recorded as "T" therein lies the problem that I cannot seem to solve.


    Thanks in advance.

  • Re: Find Max Value In Column When Not All Values Are Numbers


    While the following macro gets the value you want to see in BH145, I'm not so sure that it will always do what you want, since it examines all the cells in ET207:EX218, including the grey bits inbetween. Likewise the routine later looks at the cells EV207:EZ218, which overlaps the first range, to determine BH151. So I'm not at all sure this is what you want:



    p45cal

  • Re: Find Max Value In Column When Not All Values Are Numbers


    Thanks for the help p45cal! The code that you provided seemed to work at first but upon further inspection I noted that the value in cell bh145 and cell bh151 were the same. Now when I attempt to save the file, that is execute the code, I get values from both columns. That is, I get values from the Max Liquid Precip col and the Max Frozen Precip col combined in the cell BH151. Again thanks for the insight that you have provided thus far. Any suggestions on this annomally?

  • Re: Find Max Value In Column When Not All Values Are Numbers


    It is the following lines which determine which cells are to be examined in the two loops to decide what appears in those two results cells.


    Code
    Set lpMax = Range("ex207", Range("et" & Cells.Rows.Count).End(xlUp).Address)
    Set fpMax = Range("ez207", Range("ev" & Cells.Rows.Count).End(xlUp).Address)


    As noted earlier, ET207:EX218 and EV207:EZ218 overlap, and I think contain more columns than they should. So which columns should they be searching? You can see the relation between each 'Set' line and its corresponding address, so you should be able to adjust those lines above to look in only the columns you want them to look at, and only you know which those columns are.


    p45cal

  • Re: Find Max Value In Column When Not All Values Are Numbers


    Thanks for pointing out my obvious mistake. I have looked at it so many times that I overlooked it. I did not realize that I was looking in so many columns.


    Thanks.

  • Re: Find Max Value In Column When Not All Values Are Numbers


    Quote from Dave Hawley

    Why not use WorksheetFunction.Max(.....?


    I think he's already got the max, he's now getting the dates on which the max occurred (I think), as a list in a single cell.
    (Maybe it's a case of a badly titled question - I'm not sure)
    p45cal

  • Re: Maximum Value In Column Of Text & Numbers


    Very well put 45 cal. The goal was to not only retrieve the max value but also to get the date on which it occurred. I have went to several versions of how to do this the best way and found it necessary to first find the max values using an array formula and then use the routine created to find the date that those max values occorred on. I am still having an issue with when there is only a trace of precip for the entire month. For example is all values are "0.00" with the exception of one value of "T" then the max value should be recorded as "T" as well as the sum. Any ideas on how to incorporate something like that. Thanks for all of your help so far.


    Rex

  • 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:

    Code
    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="","",AVERAGEA(AK36:AQ128))[/bf]
    or perhaps better:
    [bf]=IF(AK36="","",AVERAGEA(AK36:AK128))[/bf]p45cal

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!