# Thread: Complex Excel Formula--need help! (Excel)

1. ## 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. ## 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. ## Re: Complex Excel Formula--need help! (Excel)

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

4. ## 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. ## 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...
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
•