Excel =VLOOKUP

[grantx]

The following is from an Excel table of tick data I'm analysing:

A .......B
751....299
751....70
752....66
752....165
752....154
752....154
752....154
752....26
752....26
752....628
752....621
752....518
752....503
753....598
753....598
753....731
753....749

Column A represents time, eg 751 = 7:51. Column B represents, for example, volume.

Via a VLOOKUP(...) table I can extract volume figures for each time increment, eg =VLOOKUP(752,A1:B17,2). However, these will always correspond to the last time values – not very accurate.

I would like to determine an average of all volumes for each time-frame. Unfortunately, as you can see the number of records within each time-frame is random.

Any suggestions appreciated.

Grant.

 

[swandro]

Try this....

=AVERAGE(IF(A4:A20=753,B4:B20))

You need to enter this as an array formula. When you have typed the above into a cell, instead of just pressing the Enter key, press Control + Shift and then the Enter key, all at the same time. Apologies if you already knew how to do this.

Cheers

Rob

 

[grantx]

.

 

[grantx]

.

 

[grantx]

Rob,

Please refer to the attached Word.doc.

Grant.

 

[swandro]

Hi Grant

Not sure why you think my formula did not work - I got it working myself using a copy of your data. I was assuming that you would copy it down and change the time in each one. But you have found an alternative solution which is OK.

However - rather than dip into VBA to tidy it up, you should look into Pivot tables. These are an absolute Excel gem and they will automatically produce all the sort of totals and averages you want.

To illustrate what they do, I have created one from the data I used to test my earlier solution. Take a look. They are a bit fiddly to learn, but if you need to do a lot of data analysis, they are essential!

See attached.

Rob

 

[grantx]

Rob,

“copy it down”? There are 840 minute values. You solution re the pivot table is fine but doesn’t it mean duplicating data? The actual sheet is big – I have to turn off auto-calc to do formulas/calc’s.

The “average” aspect of my solution can be simplified to =AVERAGE(...).

Grant.

 

[swandro]

Hi Grant

With Pivot Tables there is no duplicate data. When you create one you point it to the source data and it goes off and creates the table for you. You can put this on a separate worksheet if you want (advisable actually). Also, once built, it is automatically updated with any changes to the source data.

A pivot table is just a fancy way of automatically creating stats from raw data. It is certainly the more professional way of doing it than a VBA routine, based on your requirements.

If your worksheet is being updated in real-time, but the analysis you want is a snapshot, I would suggest that you put the analysis in a separate workbook and point it to the data in your existing workbook. Then, when you want to do the analysis, you open the second workbook when the realtime feed is off. Trying to do too many calculations may slow down your realtime capture if you are not careful.

Hope this helps

Rob

 

[TheBramble]

Mr. X, you're missing a trick if you're trying to just average those times. The number of time occs are just as (more?) important as the overall or average.

 

[grantx]

Mr Bramble,

That is precisely what “trades” designates on the Rob.doc attachment. Anyway, I wasn’t talking to you, and I fail to see what business it is of yours.

Rob,

Point taken re pivot tables.

Re using a second workbook, if I understand you correctly, a separate analysis/snapshot would be less demanding in processor terms. Further, this could be active (on/off) at 1-minute intervals if so desired.

Thanks, Rob.

Grant.

 

[TheBramble]

Mr Bramble,

That is precisely what “trades” designates on the Rob.doc attachment. Anyway, I wasn’t talking to you, and I fail to see what business it is of yours.

Mr. X, I didn't open the attachment as it was clearly addressed to someone else.

You're right, it is none of my business, but being a dog, I do tend to root around in, well, stuff like this....

Maybe IG Index themselves wil be able to assist you in your research?

 

[Sirlosealot]

Grant.

This may not be not what you're wanting exactly and sorry if it isnt. To simplify the equations in row H I've named the time and volume columns "Time" and "Vol".

Also as in swandros solution I've used "array" equations

It returns an error message if there are no time values and you could probably add to the equation a condition that returned a 0 if the original equation returned an error. If this is basically what you need perhaps someone could tell us how to do that?

Cheers

Sirlosealot

 

[grantx]

Mr Bramble,

“I didn't open the attachment as it was clearly addressed to someone else.”

Clearly, you are a Gentleman. This is reassuring because the content is confidential, controversial, compromising and alliterative.

IG Index, how so?

Sirlosealot,

Thank you for the suggestion. Please refer to the attachment. As you will see, I have used the array approach. It isn’t a question of right or wrong; it’s the willingness to help (and I need it at times) . Noted and appreciated.

Re the attachment. There is a cell which throws up an anomaly. If can spot it, you’re obviously the kind of person who assumes an air of superiority because you have a Blue Peter badge (probably stolen). However, if you can explain the anomaly, you can have my gold Blue Peter badge (I’d rather not talk about it).

Grant.

 

[TheBramble]

Mr Bramble,

IG Index, how so?

I assumed you were looking to scale up your trading to the next level and were looking for someone who will take sub-£1 bets.

 

[grantx]

Mr Bramble,

My newsagent, Mr Patel, has very tight spreads so I have neither the incentive nor see the advantage of using some southern nancy-boy ponce shop to "gear-up". The only trades these guys know are "rough trades".

Grant (Head of Arbitrage, Stockport Bee-Keeping Society).

 

[Sirlosealot]

This seems to be a Bill Gates special designed to try and get ordinary mortals to waste their time and show us who really rules the universe.

If you edit cell D6 in your spreadsheet by adding a 1 on at the end to make the equation read "=D5+0.0051" instead of "=D5+0.005" all is revealed once it has recalculated.

Maths and logic are not Mr Gates strong points.

Cheers

Sirlosealot

 

[timm]

This seems to be a Bill Gates special designed to try and get ordinary mortals to waste their time and show us who really rules the universe.

If you edit cell D6 in your spreadsheet by adding a 1 on at the end to make the equation read "=D5+0.0051" instead of "=D5+0.005" all is revealed once it has recalculated.

Maths and logic are not Mr Gates strong points.

Cheers

Sirlosealot

D5=ROUND(D4+0.005,3) and fill down. To remove the pesky floating point number problems.

 

[grantx]

Sirlosealot,

Not another. This is really bad. How long have spreadsheets been available? Yet there is a flaw in the basic function, in its raison detre – simple arithmetic. Hope to God Gates keeps away from physics and medicine – we’ll be in multi-dimensions and dead,

Why should this 108.120 + 0.005 not work – all the others do? Maybe 108.120 is a mystical (Pythagorean?) number. Perhaps I’ve discovered the key to eternal youth and beauty (not that I need it).

Here’s one: subtract any number in column D from the number above it. For every case the answer will be 0.005 but don’t bet the ranch on it. It actually turns out to be 0.00499999999999545. Even in terms of billion dollar portfolios this is insignificant. However, if “0.005” is used as part of a decision process – buy, sell, hold, liquidate – it could cause problems.

I’m afraid I’ll have to find an alternative to the gold Blue Peter badge – Konnie gave it to me (sad).

Grant.

 

[grantx]

Sirlosealot,

I can’t add 1 to the end because all cells relate to the one above, and the actual values are random so I can't single out that specific value.

Timm,

Looks like the solution. Thank you for that, mate.

Grant.

 

[new_trader]

Sirlosealot,

Not another. This is really bad. How long have spreadsheets been available? Yet there is a flaw in the basic function, in its raison detre – simple arithmetic. Hope to God Gates keeps away from physics and medicine – we’ll be in multi-dimensions and dead,

Why should this 108.120 + 0.005 not work – all the others do? Maybe 108.120 is a mystical (Pythagorean?) number. Perhaps I’ve discovered the key to eternal youth and beauty (not that I need it).

Here’s one: subtract any number in column D from the number above it. For every case the answer will be 0.005 but don’t bet the ranch on it. It actually turns out to be 0.00499999999999545. Even in terms of billion dollar portfolios this is insignificant. However, if “0.005” is used as part of a decision process – buy, sell, hold, liquidate – it could cause problems.

I’m afraid I’ll have to find an alternative to the gold Blue Peter badge – Konnie gave it to me (sad).

Grant.

Grant,

I thought I explained this to you once before. It is because 0.005 is not an inverse of a binary number. Computers work in binary and you will get this kind of anomaly when working with floating point calculations. 0.005 is the inverse of 200 which is not a binary number.

Binary numbers 2,4,8,16,32,64,128,256,512...etc..etc