average 2 numbers in one cell (Excel 2003 SP3)

[steveh]

Good morning

I am downloading via a web query some information from the web, unfortunately the site I want to use puts the predicted high and low into one cell when it comes into Excel, please see below

Weather.jpg

A) Is there a formula that I can use that can calculate the average of the numbers in each cell without first stripping them out into intermediate cells

or

B) would I have to consider using =LEFT(TRIM(B166),2) to get the first 2 numbers into one cell (although sometimes there is a single number?) and then in another cell return the next number, I guess by using a MID or RIGHT formula that I can probably work out.

(Edit: I can use something like =MID(B166,9,2) but the 9 may be 8 if the the LEFT formula is only for one number and the MID may be for 1 or 2 numbers?)

[HansV]

Here is a formula that will work regardless of the number of digits in the temperatures. With the two temperatures in A1:

=AVERAGE(--LEFT(A1,FIND("°",A1)-1),--MID(A1,FIND("|",A1)+2,FIND("°",A1,FIND("|",A1)+1)-FIND("|",A1)-2))

[HansV]

In case you're interested, here is a workbook that shows the intermediate steps that I used to create the mega-formula from my previous reply.

AverageTemperature.xls

[StuartR]

In case you're interested, here is a workbook that shows the intermediate steps that I used to create the mega-formula from my previous reply...

Thank you for sharing the method, it is a great learning aid.

What is the purpose of the -- in front of some of the formulae?

[HansV]

The -- could actually be omitted here, but it serves to turn a string value into a number. The result of LEFT(...) or MID(...) is a string. The - operator turns the string into a number, and the second - corrects the sign.

Example: LEFT(...) results in the string "23".
-LEFT(...) is the number -23.
--LEFT(...) is the number 23.

However, AVERAGE is clever enough to handle string arguments, so as mentioned, the -- aren't strictly necessary here.

[steveh]

Here is a formula that will work regardless of the number of digits in the temperatures. With the two temperatures in A1:

=AVERAGE(--LEFT(A1,FIND("°",A1)-1),--MID(A1,FIND("|",A1)+2,FIND("°",A1,FIND("|",A1)+1)-FIND("|",A1)-2))

Hi Hans

That is amazing, thank you very much, also thanks for the explanation which amazingly I can understand (except the -- which you have explained but I am trying to fully digest). I was playing with VALUE etc. and would still be at that stage in a month of Sunday's

[steveh]

Here is a formula that will work regardless of the number of digits in the temperatures. With the two temperatures in A1:

=AVERAGE(--LEFT(A1,FIND("°",A1)-1),--MID(A1,FIND("|",A1)+2,FIND("°",A1,FIND("|",A1)+1)-FIND("|",A1)-2))

Hi Hans

I have been trying to follow the steps of your mega formula breakdown with little (make that no) success, so I have tried to amend the above formula to do what I want.

The circumstances are slightly different in that the cells contain this type of format 85°F 29°C / 68°F 20°C and what I need is the average of the °C temperatures so I have amended the formula to

=AVERAGE(--LEFT(M2,FIND("°C",M2)-1),--MID(M2,FIND("/",M2)+2,FIND("°C",M2,FIND("/",M2)+1)-FIND("/",M2)-2))

but I get a #Value! error

[HansV]

The formula becomes quite long (this is for a text value in A2):

=AVERAGE(--MID(A2,FIND("°F",A2)+3,FIND("°C",A2)-FIND("°F",A2)-3), --MID(A2,FIND("°F",A2,FIND("°C",A2)+2)+3, FIND("°C",A2,FIND("°C",A2)+2)-FIND("°F",A2, FIND("°C",A2)+2)-3))

I have attached a workbook with the intermediate steps.

CelsiusFahrenheit.xls

[steveh]

The formula becomes quite long (this is for a text value in A2):

=AVERAGE(--MID(A2,FIND("°F",A2)+3,FIND("°C",A2)-FIND("°F",A2)-3), --MID(A2,FIND("°F",A2,FIND("°C",A2)+2)+3, FIND("°C",A2,FIND("°C",A2)+2)-FIND("°F",A2, FIND("°C",A2)+2)-3))

Absolutely awesome

Thank you very much indeed Hans