Can you use MAX function on numbers/text?

[Ogbuehi]

I am trying to use the "MAX" function to find the max temp on a particular day in a week from 7 separate sheets. The problem I have is one particular company autopopulates their temperature block with the temp and degree symbol: 46°F instead of just 46. The max function is thrown off it appears by the symbol and letter. Is there a way to get the max function to only look at the numbers? I'm also open to the option of autoconverting each temp to a number on the master sheet and letting the "MAX" function search through those if that's possible. Thanks in advance.

[pike]

Hi Ogbuehi
there will be better way suggested but you could try....

Please Login or Register  to view this content.

this will just leave the numbers
hope it helps

[teylyn]

Hi,

try this: With your data in A2 to A100

=MAX(VALUE(SUBSTITUTE(A2:A100,"°F","")))

this is an array formula and must be confirmed with Ctrl-Shift-Enter

If you don't want to use the array formula, put this into a helper column and copy down

=SUBSTITUTE(A2:A100,"°F","")

then run a MAX() on the values in the helper column.

hth

[pike]

Hey Ogbuehi
if you cant use a helper column use ...

Please Login or Register  to view this content.

[Gerson Pineda - E.C.]

Hey Ogbuehi
Here a different way to find the same result, introduces CTRL + Shift + Enter
=MAX(VALUE(LEFT(A1:A9,FIND(" °F",A1:A9,1)-1)))

best regards from Honduras

[Ogbuehi]

Okay I tried Gerson's and Teylyn's solution and neither worked. I've attached the excel spreadsheet with the results of both of their formula's. I don't know how to utilize what you've done Pike. Do I just type that entire code into one cell? You guys have really been great help. Maybe I just mis-typed something.

[teylyn]

Hi,

the array formulae won't work in a 3D range, i.e. if the cells involved are all on different sheets. You need to extract the numb on the sheet like this in 'Day1:Day8'!A4:

=VALUE(SUBSTITUTE(A3,"°F",""))

Then on the master sheet you can do

=MAX('Day1:Day8'!A4)

hth

[Ogbuehi]

The only problem I have with that is I copy and paste over the sheets every day or couple of days as I get the reports. How would I prevent that extraction formula from being erased everytime a new report is pasted over it? I know I could manually repaste that formula, but this report is being setup for people with way less excel knowledge than myself. That's why I'm trying to setup everything from the master sheet. It's the only sheet that won't change from week to week as we create a new report.

[teylyn]

If the temperatures in question are always in the same cell in the daily sheets, you can pull the values into the master sheet like this:

On your master sheet create a section where you pull the data from the different sheets, e.g.

in K1 to K8:

='Day1'!A3
='Day2'!A3
='Day3'!A3
='Day4'!A3
='Day5'!A3
='Day6'!A3
='Day7'!A3
='Day8'!A3

then use in Master sheet cell A2

=MAX(VALUE(SUBSTITUTE(K1:K8,"°F","")))

confirm with CSE!!

Will that work?

[Ogbuehi]

That works perfectly. Problem solved. I just had a question about that formula. At first, I couldn't get it to work (it kept displaying "VALUE"). Then I read your instructions a little closer and noticed the Ctrl-Shift-Enter part and that solved it. What does that do and is it possible to just type those special brackets on the formula vice having to press those keys? Thanks for all the help.

Won't let me give you credit for helping me because I have to spread it around. Sorry.

[teylyn]

This is called an array formula. It evaluates not just one cell but a set of cells. Array formulae must be confirmed with Ctrl-Shift-Enter. Excel will then put the curly braces around them, so when you examine the formula you can see that it's an array formula.

is it possible to just type those special brackets on the formula vice having to press those keys?

No, you can not type them yourself! That would effectively make your formula into useless text.

If you are interested in a bit more information about arrays formulae, read this:

http://www.xtremevbtalk.com/showthread.php?t=296012

hth