You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to calculate average currency convertion for a given period in numbers?

How can I calculate the average currency conversion from € to $ for a given period, month?

iMac 21.5″, macOS 10.13

Posted on Feb 3, 2023 1:18 AM

Reply
Question marked as Top-ranking reply

Posted on Feb 3, 2023 2:47 AM

Here are a couple of ways for getting an average for the month of January 2022.


Find the appropriate symbol for Yahoo! Finance, from which Numbers gets its data, and retrieve the historical rates like this:




The formula in B2, filled down is:


=STOCKH(B$1,0,A2)


Use =STOCKH(B$1;0;A2) if your computer is set to a region that uses , as a decimal separator.


The red warning triangles denote days for which there is not trading data (typically weekends, holidays).


Row 33 is defined as a Footer Row so that a formula in it can refer to the column directly above it.


To get the average for the trading days (ignoring the non-trading days) use this formula in B33:


=AVERAGEIF(B,">"&0,B)


Or if your region uses , as a decimal separator:


=AVERAGEIF(B;">"&0;B)



Another, even easier way that doesn't require formulas (done in a few seconds), is to click in the data table and from the menu choose Organize > Create Pivot Table > On Current sheet.


Then drag the fields in the Pivot Options pane on the right as shown here:





A Pivot Table can be particularly efficient if you extend your data (just add more rows with dates and fill the formula down) to include multiple months as it can easily calculate by month (or week, or quarter or year, etc.)





SG

6 replies
Question marked as Top-ranking reply

Feb 3, 2023 2:47 AM in response to JCordova52

Here are a couple of ways for getting an average for the month of January 2022.


Find the appropriate symbol for Yahoo! Finance, from which Numbers gets its data, and retrieve the historical rates like this:




The formula in B2, filled down is:


=STOCKH(B$1,0,A2)


Use =STOCKH(B$1;0;A2) if your computer is set to a region that uses , as a decimal separator.


The red warning triangles denote days for which there is not trading data (typically weekends, holidays).


Row 33 is defined as a Footer Row so that a formula in it can refer to the column directly above it.


To get the average for the trading days (ignoring the non-trading days) use this formula in B33:


=AVERAGEIF(B,">"&0,B)


Or if your region uses , as a decimal separator:


=AVERAGEIF(B;">"&0;B)



Another, even easier way that doesn't require formulas (done in a few seconds), is to click in the data table and from the menu choose Organize > Create Pivot Table > On Current sheet.


Then drag the fields in the Pivot Options pane on the right as shown here:





A Pivot Table can be particularly efficient if you extend your data (just add more rows with dates and fill the formula down) to include multiple months as it can easily calculate by month (or week, or quarter or year, etc.)





SG

Feb 3, 2023 3:35 AM in response to JCordova52

JCordova52 wrote:

So you are using GBPUSD=X as a Stock symbol?


Yes. That's the symbol Yahoo! Finance uses, and it works in STOCKH. I couldn't get the CURRENCYH function to work to pull historical quotes.


If you are using this, say, for tax purposes, then best check with other sources just to make sure that the rates are good enough approximations.


SG

Feb 3, 2023 5:21 AM in response to JCordova52

JCordova52 wrote:

Privot concept is FANTASTIC


Yes, Pivot Tables are surprisingly easy to use (no formulas to debug!) and particularly handy for summarizing data by date range as you are doing here. Plus they don't mess with the original data.


Don't forget to "Refresh" (click in Pivot Table and choose Organize > Refresh Pivot Table) after you make any changes to the source data. The Pivot Table will then update its values and expand automatically if needed.


SG


How to calculate average currency convertion for a given period in numbers?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.