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) ??
Obtaining average from another WS
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Obtaining average from another WS
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 12639
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Obtaining average from another WS
=AVERAGE(WebData!C36,WebData!C37) works for me
You do not have the required permissions to view the files attached to this post.
StuartR
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Obtaining average from another WS
This should work too:
=AVERAGE(WebData!C36:C37)
=AVERAGE(WebData!C36:C37)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Obtaining average from another WS
Thanks StuartStuartR wrote:=AVERAGE(WebData!C36,WebData!C37) works for me
I don't know if it is a version difference because I only get #DIV/0! when I use this?
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Obtaining average from another WS
Thanks HansHansV wrote:This should work too:
=AVERAGE(WebData!C36:C37)
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; )
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Obtaining average from another WS
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.
- Select Edit | Replace...
- Enter °C in the Find what box.
- Leave the Replace with box blank.
- Click Replace All.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Obtaining average from another WS
Hi HansHansV wrote: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.
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)))
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Obtaining average from another WS
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).
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).
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Obtaining average from another WS
Thanks for that HansHansV wrote: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).
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 )
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Obtaining average from another WS
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.
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.
Best wishes,
Hans
Hans