Help with Excel (advanced)?

Help with Excel (advanced)?

by Tradingrichmom

Question by jdpicou1: Help with Excel (advanced)?
I am building a spreadsheet to backtest Forex trading strategies.

I want to be able to find the difference between the circled values. Ideally to put the value in cell L93.
http://img205.imageshack.us/img205/4206/yahooal4.jpg

The only hard part is that there is not always 3 rows between each number to be used. Sometimes there may only be 1 row, or there may be 10 rows.
Is there a way for excel to fetch values between two of the same number? Like between 0's?
I am asking b/c I need to do this hundreds or maybe thousands of times. I can find the difference between the sums of each of the two columns easily, but I want to be able to do this also.

Thanks


Best answer:

Answer by Gary E
It can be done with a macro, of course.

But if
1. There is a column to spare somewhere outside of the normal viewing and print areas.
2. The difference you want to display in Column L is to be in every row where there is a positive value in Column K
3 The difference amount is to be the last previous positive value from Column J minus the value in Column K of the display row.
Then
You can do it with just two formulas (copied to the full columns).

I have selected Column Z for the intermediate column and Row 85 to enter the formulas.

In Cell Z85 enter
=IF(J85>0,J85,Z84)

In Cell L85 enter
=IF(K85>0,Z85-K85,"")

Copy each of theses formulas in their respective column for the full range of the data and you will see differences where they belong.

You may need to adjust the formula at the beginning of the data or anywhere there is a break in the data, but you don't show any of those so I can't tell you what would be required. Hopefully you can figure it out. Or you can add to your question to request further assistance.

What do you think? Answer below!