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

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

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

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