Results 1 to 12 of 12

20050816, 17:57 #1
 Join Date
 Aug 2005
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Complex Formulaneed help! (Excel)
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

20050816, 19:28 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Complex Formulaneed help! (Excel)
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.Jerry

20050817, 14:30 #3
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Complex Formulaneed help! (Excel)
Hi There
Could you attach the file please. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>Jerry

20050817, 14:31 #4
 Join Date
 Aug 2005
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Formulaneed help! (Excel)
Edited by HansV to remove request for direct email assistance  see <!rule=10>Rule 10<!/rule>.
Here is a sample what this database would look like. Thanks for your help!

20050817, 14:32 #5
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Complex Formulaneed help! (Excel)
ignore last, have got it now
Jerry

20050817, 14:37 #6
 Join Date
 Aug 2005
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Formulaneed help! (Excel)
Forgot to mention....the actual spreadsheets I will be working with will each have more than 500 assets (rows) and 23 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....

20050817, 16:20 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Complex Formulaneed help! (Excel)
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.

20050817, 16:47 #8
 Join Date
 Aug 2005
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Formulaneed help! (Excel)
Thanks so much for your helpI 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>

20050817, 17:00 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Complex Formulaneed help! (Excel)
Do you want three (or more) days with two (or more) equal increments?
Are 1) and 2) separate, or must both be satisfied?

20050817, 17:05 #10
 Join Date
 Aug 2005
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Formulaneed help! (Excel)
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.

20050817, 17:10 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Complex Formulaneed help! (Excel)
OK, use this formula: =AND(C2B2=B2A2,ABS(C2B2)>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).

20050830, 16:55 #12
 Join Date
 Aug 2005
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Formulaneed help! (Excel)
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!