title: Is there a way to have the MAX function read both text and numbers?
body: <p>I currently have a spreadsheet where I am collecting data each week. I want to find the max value in each week and record it automatically. </p>
  
  <p>The issue is that I am concatenating an asterisk to the end of the data point if data hasn't been recorded that week for that specific station. So if the previous week I recorded 50, and this week I didn't record anything for that station, it auto fills the data point with 50* to signify I didn't look at that station and that that information is auto filled from the previous week.</p>
  
  <p>I'm using this function to find the MAX in each column:</p>
  
  <pre><code>=IF(MAX(K6:K54)=0,OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),MAX(K6:K54))
  </code></pre>
  
  <p>and this is an example of a data point in that column:</p>
  
  <pre><code>=CONCATENATE(SUBSTITUTE(IF(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=0," ",CONCATENATE(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),"*")),"*",""),"*")
  </code></pre>
  
  <p>Essentially what is happening is that the MAX value is ignoring a value with an asterisk, which I want it to include in each column. If I have a column of 41, 32, 46*, and 52*, it says the max is 41, when I want it to say 52. Not sure if this is possible, but maybe I haven't thought of something. Anything helps at this point. Thanks :)</p>