Complex Formula--need help! (Excel)

[lahokeness]

Hi,

I am working on a project that entails comparing daily prices for 200+ bonds, organized by date. Is there such a formula that exists that can help me to do the following:

1. Find three or more consecutive days where there are equal increments (+ or -) of bond prices fluctuated?
AND
2. Find three or more consecutive days where these prices fluctuated (equally) by more than $1.00 at a time?

If ANYONE has any guidance on this, it would be greatly appreciated, as finding this answer would save me days of extra manual work!

Thanks much,
Lori

[Jezza]

Lori

Any chance you can provide an example of your workbook, cut down of course with any incriminating information removed and I will see what I can do.

[Jezza]

Hi There

Could you attach the file please. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

[lahokeness]

Edited by HansV to remove request for direct e-mail assistance - see <!rule=10>Rule 10<!/rule>.

Here is a sample what this database would look like. Thanks for your help!

[Jezza]

ignore last, have got it now

[lahokeness]

Forgot to mention....the actual spreadsheets I will be working with will each have more than 500 assets (rows) and 2-3 month increments.

I have a feeling there are few instances of 'smoothing' that are $1.00 or more....may be better to use any increments that are $0.25 or more....

[HansV]

You can use conditional formatting for this. See attached version. The conditional formatting was applied in one go, by selecting D2:K15.

Note: I took "three consecutive days" literally (i.e. two increases/decreases). If you meant 4 days (3 increases/decreases), conditional formatting should start in column E, and the formulas should be extended.

[lahokeness]

Thanks so much for your help-----I am confused at how you came about that formula, and was wondering if you could run through your process again. I have updated a model, that does in fact include examples of "equal increments of three or more days," as I realized later my censor may have caused confusion before. Here's a quick rundown of what I am looking for again:

1. A way to determine which funds raised (or declined) consecutively for three or more days at a time in EQUAL increments each day.
2. The increments that match the above criteria much be more than 25 cents each day.

Again, any (fast!) help is appreciated, as I'd like to propose this idea yet today to my manager. And please do not hesitate to ask for further clairification if necessary. You guys are the best!!

Thanks,
Lori

<img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

[HansV]

Do you want three (or more) days with two (or more) equal increments?
Are 1) and 2) separate, or must both be satisfied?

[lahokeness]

Yes, three+ days with two+ increments is what we're looking for.
So, for example :
Day 1= .50 Day 2= .75 Day3=1.0

1) and 2) must both be satisfied.

[HansV]

OK, use this formula: =AND(C2-B2=B2-A2,ABS(C2-B2)>0.25)

It says the increment from B2 to C2 must be the same as that from A2 to B2, and the absolute value of the increment mist be more than 0.25.

See attachment. In this version, the middle of the 3 days is highlighted (in the previous version, the last day of the 3 was highlighted).

[lahokeness]

Hans, you are the MAN!

Sorry it has taken me so long to reply and give you major props for figuring out this formula for me. You have decreased my workload this week by at least 20 hours!!

Thanks again!