compare formula

[tacollier]

I am in a bind and need some excel guru to help!! I have a a bunch of data in one column that I want to compare to each other starting at the bottom. I want to take the very last cell and the cell above it and see if those two numbers are a 2% difference up or down. Then I want to take the 2nd from the bottom number and compare that to the 3rd from the bottom # and see also if those 2 #'s are more then 2% or more apart. I would like to just know how many times it occurs in my data set not any particular time it occurs.

Thanks!!

 

[T. Valko]

I am in a bind and need some excel guru to help!! I have a a bunch of data in one column that I want to compare to each other starting at the bottom. I want to take the very last cell and the cell above it and see if those two numbers are a 2% difference up or down. Then I want to take the 2nd from the bottom number and compare that to the 3rd from the bottom # and see also if those 2 #'s are more then 2% or more apart. I would like to just know how many times it occurs in my data set not any particular time it occurs.

Thanks!!

2% of what?

Let's assume this is your data:

Book1
	A	B
1	41
2	35
3	40
4	38
5	34
Sheet1

What results would you expect in column B?

 

[shg]

       -A-- -B--
   1   Data > 2%
   2     93     
   3    110  x  
   4    103  x  
   5    105     
   6     93  x  
   7    110  x  
   8     92  x  
   9    109  x  
  10    104  x  
  11    109  x  
  12    102  x  
  13     95  x  
  14    105  x  
  15    103     
  16    106  x  
  17     99  x  
  18     94  x  
  19     95     
  20     91  x  
  21    103  x  
  22    103     
  23     93  x  
  24    107  x

The formula in B3 and copied down is

=IF(MAX(A2:A3)/MIN(A2:A3) - 1 > 2%, "x", "")

A single formula to return the number of occurrences is

=SUMPRODUCT(--(IF(A3:A24>A2:A23, A3:A24/A2:A23, A2:A23/A3:A24) - 1 > 2%))

... which MUIST be confirmed with Ctrl+Shift+Enter.

 

[tacollier]

tvalko,

I want to look to see if a4 is 2% higher then a5 and i want to know is a3 is 2% higher then a4 and so on and so forth. I know originally i said up or down but i think i really need to know just up.

 

[tacollier]

shg,

Thanks for the response, but how would i change the formula if i wanted to just know if it were 2% higher and didnt care if it were 2% lower? Here is a sample of my data from column k.

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" align="right" height="20" width="64">1319.68</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1325.83</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1327.22</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1286.12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1257.64</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1180.55</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1183.26</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1141.2</td> </tr> </tbody></table>

 

[tacollier]

also the sumproduct formula didnt work for me