How do I create a formula

[Daniel]

I am trying to creat a formula to search my database by columns,

I have columns of numbers per hour such as:
Hr 1 2 3 4 5 6 7 8 9 10
11 12...
1/1/2002 100 235 200 215 300 320 300 210 177 150 130 110 ...
1/2/2002 102 210 190 102 77 65 21 100 105 102 125 275..


I have a value for each Hour of the day in the rows the days are lined down
the columns. i am trying to write a formula to return a signal value to the
user to let them know that if in that day the values per hour are a cycle. A
cycle for me is if the value drops 150 points with fours hours which would be
4 cells next to each other. Does anyone have a clue what I'm talking about?
Any help is appreciated

[Fredrik Wahlgren]

"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:B9C3A99F-F15C-4D87-9322-5BF12D5B3C10@microsoft.com...
> I am trying to creat a formula to search my database by columns,
>
> I have columns of numbers per hour such as:
> Hr 1 2 3 4 5 6 7 8 9 10
> 11 12...
> 1/1/2002 100 235 200 215 300 320 300 210 177 150 130 110 ...
> 1/2/2002 102 210 190 102 77 65 21 100 105 102 125 275..
>
>
> I have a value for each Hour of the day in the rows the days are lined
down
> the columns. i am trying to write a formula to return a signal value to
the
> user to let them know that if in that day the values per hour are a cycle.
A
> cycle for me is if the value drops 150 points with fours hours which would
be
> 4 cells next to each other. Does anyone have a clue what I'm talking
about?
> Any help is appreciated
>
>

Is your condition satisfied somewhere. Can you provide an example?

/Fredrik

[Pow!]

From your example above, the easiest way is to drop in a conditional format:

highlight the top, leftmost cell of your data (value = 100 in your example, cell b2 in my paste of your example into a blank sheet)

select Format > Conditional Formatting...

Choose "cell value is" in the first box, "greater than or equal to" in the second box and in the third add =e2+150 where "e2" is the cell that is four spaces from the cell you're modifying and "150" is the size of the drop that you're looking for. Note that the "e2" has no "$" so that it is a relative, not absolute reference. This last point is very important.

Next click the Format button on the conditional formatting dialog box and under the Patterns tab choose an easily spotted color, like red. Now click OK on the two dialog boxes and it will return you to the spreadsheet.

Finally, select the cell you modified (b2 in my example) and copy. Now highlight the remainder of your data and choose Edit > Paste Special > Formats. Those elements that are 150 above the item four hours later will turn red. In the sample data you shared, the only one is hour 7 on Jan 1, 2002.

You will get some false positives in the rightmost columns if they exceed 150 (because they will be 150 points greater than the blank cell 4 spaces to their right). You can address this by not including those cells in the highlight before you do the paste special.

Did that do the trick?

cheers,
Andre