Excel =VLOOKUP

[new_trader]

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

Grant,

This solution is OK, but if you are going to use a logical test such as =,<,> and your data increments in 0.005 steps, you will be better off using:

D5=ROUND((D4/0.005),0)*0.005

if I remember rightly, this is where you had trouble last time with an 'IF' condition.

 

[timm]

Grant,

This solution is OK, but if you are going to use a logical test such as =,<,> and your data increments in 0.005 steps, you will be better off using:

D5=ROUND((D4/0.005),0)*0.005

if I remember rightly, this is where you had trouble last time with an 'IF' condition.

D5=(ROUND((D4/0.005),0)+1)*0.005

 

[new_trader]

D5=(ROUND((D4/0.005),0)+1)*0.005

No

 

[grantx]

NT,

Yes, you did explain it, and I remember it. I was simply reiterating the problem as an illustration. But does it explain why all the other values work - 108.140, 108,145, etc but not 108.120?

Will reply re the other posts later.

Grant.

 

[grantx]

Timm and NT,

Please explain the flaw in each as you see it.

Grant.

 

[timm]

OK. What you are doing is incrementing in steps of 1/200. As you are aware, Excel isn't very good at maths so can't handle this without help.

My first suggestion was to simply round to 3dp at each calculation to stop the error compounding until it causes a problem. NT has helpfully pointed out the more generic approach for doing fractions in Excel:-

First convert to an integer. Then do your calculation. (Excel can handle integer maths) In this case just increment by 1. Then convert back to a fraction,

D5=(ROUND(D4*200,0)+1)/200

This approach also works where the fraction can't be rounded to a decimal, e.g. to increment by 5/60,

D5=(ROUND(D4*60,0)+5)/60

 

[grantx]

Timm,

200 not a binary, 1/200 = 0.0005 (derived from a binary), etc. I’ve got it now.

Thanks for the help and explanation, mate.

Grant.

 

[Lucky_81]

I dont know whether you have the answer already but if you haven't this would do the trick.

=ROUND(SUMIF(A1:A17,H2,B1:B17)/COUNTIF(A1:A17,H2),2) where h2 is the time i.e. cell h2 contains 752. If you wanted you could create a simple input box that asks for the time frame your looking at which then inserts the time in cell h2 (or whatever cell you want to reference from).

Hope this is of some help.

 

[grantx]

Lucky,

Problem is resolved, but thank you for the suggestion. I've actually your suggestion elsewhere.

Grant.

 

[Lucky_81]

No problem.

If anyone else needs any advice on excel or vba, either post or pm me and i'll try to help.