CODEWITHSUNDEEP
excelvba
starter1011
starter1011

Reputation: 35

Finding the average of mixed data

I have the following set up in my worksheet:

Currency, Return after 1 minute, 5 minutes, 30 minutes, 1 hour, 2 hours, 4 hours, 8 hours and 24 hours.

There are a lot of lines like this. I need to take the average of all the 1 minute returns of a particular currency.

For example, my first and second columns look like this:

EUR, .01

GBP, .02

USD, -.01

JPY, -.01

AUD, .02

EUR, .01

How do I consolidate this information in a new table that has the currencies as the headers, and the times as the rows, with the average of the returns by time.

Like this:

Time - Eur - Chf - Gbp - Jpy

1m -- .01 -- .01 -- .01 --. 01

Thanks!

Upvotes: 0

Views: 135

Answers (2)

Brian Gerhards
Brian Gerhards

Reputation: 849

With all of the data being provided in two columns, you may run into an issue where you can't exactly know what the data is representing. I added a third column for timing. Please consider it. Your function for the columns will be similar to the following:

https://drive.google.com/file/d/0BxYswwHVWfSvRENCd1ZZbTNpckk/view?usp=sharing

=AVERAGEIFS($B:$B,$C:$C, E3,$A:$A,"EUR")

Download the file to your machine to check it out.

A pivot table may be nice if you want to create one and can add additional flexibility to adding additional data with ease.

Please advise if you were going for a VBA macro and I will put one together for you.

Upvotes: 1

Fabrizio
Fabrizio

Reputation: 662

your table is symple, why you don't use one pivot, it show exactly what you want (like your example)

enter image description here

Upvotes: 1

Related Questions