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

    Complex Excel Formula--need help! (Excel)

    I have a very large spreadsheet with daily (bond) prices, and am trying to find an easier way to research the degree the bond has fluctuated from day 1 to day 2. Any ideas of a conditional (?) exel formula I can apply that will highlight those examples of bonds varying (+/-) 40% or more in two days?

    Help is appreciated. Otherwise I will have to go get another pot of coffee and stay up all night. [img]/forums/images/smilies/wink.gif[/img]

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

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

    You can probably use conditional formatting. Could you attach a small sample workbook? (the data can be fictional, as long as it gives us an idea of what you're working with)

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

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

    Here is a (very) small sample portion of the database...

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

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

    See the attached modified version. Conditional formatting was applied as follows:
    Select B2:S24, then select Format | Conditional Formatting...
    Selected Formula Is, then enter the formula

    =OR(B2/A2<0.6,B2/A2>1.4)

    Click Format... and set a background color in the Pattern tab.
    Finally, click OK twice.
    If a cell is highlighted, it differs by more than 40% from the cell to the left of it.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    Select B2:S24

    Format - conditional formatting...
    formula is
    =ABS(B2-A2)/B2>=40%
    <format>
    [Set format for 1 day +/- 40%]
    <ok><ok>

    Select C2:S24
    Format - conditional formatting...
    <add>
    formula is
    =ABS(C2-A2)/C2>=40%
    <format>
    [Set format for 2 day +/- 40%]
    <ok><ok>

    Steve

Posting Permissions

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