Using a function in an IF/THEN function?

[amoxuxu]

Hi all,

I have a problem analysing some biological data which describes the movement of different individual particles over time.

Some example data below (and example excel attached):

Particle Time (s) X Y Displacement (µm)
1 0 19.1 32.8 #VALUE!
1 1 19.1 32.7 0.05
1 2 19.1 32.8 0.11
1 3 19.0 32.8 0.04
1 4 19.0 32.9 0.06
1 5 19.1 32.7 0.17
1 6 19.1 32.8 0.11
2 0 33.1 30.6 14.22
2 1 33.1 30.6 0.03
2 2 33.1 30.6 0.00
2 3 33.1 30.6 0.02
2 4 33.1 30.6 0.04
2 5 33.2 30.6 0.06

I am interested in calculating the displacement of the particles over time, so using the X/Y values, I derive the displacement in column D using the X/Y values for the particle at t = x and x-1, e.g. =SQRT(((C3-C2)^2)+((D3-D2)^2))

However, using this formula alone, I get large displacement values in between the last time point of one particle, and the first time point of the next particle. e.g. see Particle 2, Time 0 above - The displacement highlighted in bold should = 0, as with all future particles where time = 0.

To get around this, I thought of using the IF/THEN function, so if time = 0, the displacement also = 0, and in other cases, use the formula above to calculate displacement.

e.g. =IF(B9=0,"0","=SQRT(((C6-C5)^2)+((D6-D5)^2))")

Using a formula in the IF/THEN function doesn't seem to work for me. Is this possible?

If not, does anyone have any suggestions for a formula that achieve what I would like? In the real data there would be thousands of rows of data so doing the analysis wouldn't be a viable option...

Apologies for the ramble, I hope that makes sense, but please ask for any clarification.

Thanks!

[Hesamexcel]

Hi,
You have added an "="
Do you mean this?

=IF(B2=0,0,SQRT(((C2-C1)^2)+((D2-D1)^2)))  

[Glenn Kennedy]

Use this:

=IF(B2=0,0,SQRT(((C2-C1)^2)+((D2-D1)^2)))

[amoxuxu]

Hi, thank you so much for the speedy response! The code works perfectly!

I wonder, whether you may be able to help me with a follow-up question now.

If I would now like to sum the displacements of each particle in a separate column, would there be a simple formula for that?

Cheers!

[Glenn Kennedy]

Yes. With a listof particle numbers, starting in K2, use this in L2:

=SUMIF(A:A,K2,E:E)

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[amoxuxu]

Yes. With a listof particle numbers, starting in K2, use this in L2:

=SUMIF(A:A,K2,E:E)

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Amazing, that's so helpful! Thank you so much