Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Formula--need 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

  2. #2
    Platinum Lounger
    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 Formula--need 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

  3. #3
    Platinum Lounger
    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 Formula--need help! (Excel)

    Hi There

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

  4. #4
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula--need help! (Excel)

    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!

  5. #5
    Platinum Lounger
    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 Formula--need help! (Excel)

    ignore last, have got it now
    Jerry

  6. #6
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula--need help! (Excel)

    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....

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Formula--need 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.

  8. #8
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula--need help! (Excel)

    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>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Formula--need 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?

  10. #10
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula--need 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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Formula--need help! (Excel)

    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).

  12. #12
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula--need 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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •