CODEWITHSUNDEEP
excelvba
user1195756
user1195756

Reputation: 21

conditional average

I have an excel sheet with data coming from an external device looking like this:

|  21.02.2011 3:55  |   4.55 |
|  21.02.2011 4:55  |   4.98 |
|  21.02.2011 5:55  |   4.60 |
|  22.02.2011 2:45  |   3.89 |
|  22.02.2011 3:45  |   3.97 |
|  22.02.2011 4:56  |   4.01 |
|  ...              |   ...  |

The date data is not formatted as such but just as text. What I want to do is to average the values in the right column per day and write this values in separate columns so it looks something like this:

|  21.02.2011  |   4.71 |
|  22.02.2011  |   3.96 |
|  ...         |   ...  |

Until now I used the split function to get the day from the left column and was thinking on doing a conditional of some sort to average the data in the right column that comes from the same day. I'm not familiar with manipulating excel from vba so any help in this important issue is highly appreciated (have hundreds of data).

Many thanks, Felipe.

Upvotes: 2

Views: 418

Answers (1)

brettdj
brettdj

Reputation: 55702

You can do this manually quite easily with a PivotTable (can be coded if needed)

  1. Extract the date to column C with =LEFT(A2,FIND(" ",A2))

  2. Add a PivotTable (Debra Dalgleish has an excellent reference here)

    • row label is column C 'Date"
    • average the values in column B "Values"

Step1 Step1

Step 2 Step2

Upvotes: 2

Related Questions