Obtaining average from another WS

[steveh]

Morning all

I am struggling to obtain the average of 2 numbers from one sheet to another but can't seem to get the formula right, for example I have tried =Average(WebData!,C36,C37) & =Webdata!,average,C36,C37. =AVERAGE(WebData!C36,WebData!C37) ??

[StuartR]

=AVERAGE(WebData!C36,WebData!C37) works for me

[HansV]

This should work too:

=AVERAGE(WebData!C36:C37)

[steveh]

=AVERAGE(WebData!C36,WebData!C37) works for me

Thanks Stuart

I don't know if it is a version difference because I only get #DIV/0! when I use this?

[steveh]

This should work too:

=AVERAGE(WebData!C36:C37)

Thanks Hans

But this also returns the #Div error? I thought that maybe it was because I was trying to average data obtained with a web query but even when I copy it to a new Workbook without the links it does the same. I have attached the copy in case it is just me be a #Div ( http://onlineslangdictionary.com/definition+of/div" onclick="window.open(this.href);return false; )

[HansV]

The values in WebData are text values, not numeric values. You could remove °C from all cells on the WebData sheet:
- Select Edit | Replace...
- Enter °C in the Find what box.
- Leave the Replace with box blank.
- Click Replace All.

[steveh]

The values in WebData are text values, not numeric values. You could remove °C from all cells on the WebData sheet:
- Select Edit | Replace...
- Enter °C in the Find what box.
- Leave the Replace with box blank.
- Click Replace All.

Hi Hans

I knew it would be me!! I didn't even give that a thought, the problem in doing what you suggest though is that each time the web query is refreshed (on open) it will put the ^o back in to the WebData sheet, can I somehow use the Value and Substitute commands in the formula, I tried this but it did not work =VALUE(SUBSTITUTE(WebData!C36:C37,"°C","",AVERAGE(WebData!C36:C37)))

[HansV]

That doesn't make sense, you have to take the average of the values, not the value of the average.

You can use

=AVERAGE(VALUE(SUBSTITUTE(WebData!C36,"°C","")),VALUE(SUBSTITUTE(WebData!C37,"°C","")))

as a 'normal' formula, or

=AVERAGE(VALUE(SUBSTITUTE(WebData!C36:C37,"°C","")))

as an array formula (confirm with Ctrl+Shift+Enter).

[steveh]

That doesn't make sense, you have to take the average of the values, not the value of the average.

You can use

=AVERAGE(VALUE(SUBSTITUTE(WebData!C36,"°C","")),VALUE(SUBSTITUTE(WebData!C37,"°C","")))

as a 'normal' formula, or

=AVERAGE(VALUE(SUBSTITUTE(WebData!C36:C37,"°C","")))

as an array formula (confirm with Ctrl+Shift+Enter).

Thanks for that Hans

Is there a 'pecking' order in general as to what comes first in a formula or is just logical (to you guys in MVP land )

[HansV]

Your 'temperatures' are text values.
You have to remove °C first, so SUBSTITUTE is the inner function in the formula.
Next, you must convert the resulting string to a number, so VALUE goes around SUBSTITUTE.
Once you have numeric values, you can compute the average, so AVERAGE goes around VALUE and becomes the outer function.