Help creating a specific formula

irishshopper

New member
Joined
Feb 4, 2017
Messages
2
Reaction score
0
Points
0
Location
Northern Ireland
Excel Version(s)
Office 16
I have an excel sales report listing in a single column sales in one of 5 currencies (US$, CA$, AUS$, GBP, Euro). I would like a formula to calculate the GBP equivalent of each sales total and put than in the same row but next column.

In the spreadsheet on the day go to run the report would input the current exchange rate for the 4 non GBP exchange rates against the GBP.

Can a formula or macro be used to do all this ?

Will attach a sample of this but reproduce it below as well.

Thank you.

A B C D

Sales record numberTotal pricePrice in GBPConversion Rates on date of report
6844£17.50 AU $ to GBP0.60
6845£5.99 US $ to GBP0.80
6846AU $16.37 CA $ to GBP0.61
6847EUR 87.00 EUR to GBP0.85
6848£10.99
6849£7.99
6850£35.50
6851£10.99
6852US $31.29
6853EUR 45.47
6854AU $33.91
6855AU $214.11
6856CA $34.70
GB TOTAL
See spreadsheet above with actual data.
Need formula to calculate the GBP value of each order and put this in Column C.
Each time I run this Report will input in column G the current exchange rates for the 4 foreign currencies.
Hope above is clear.
 
Just to clarify:
  1. How is the column with the amounts formatted? Are all cells formatted as currency, or text?
  2. Are you asking us to facilitate the update of the conversion rates, or do you already have that in place?
 
Back
Top