Fluctuations

[beavis]

Hi everyone.

Sorry if this topic already exist or if I am posting in the wrong place, I haven't found it through searching though.

My problem is that I have a number of values, that is the waterlevel of a river by the hour every hour, counting back a number of years.

I want to count how many fluctuations there is in a year for example, i.e. how many ups and downs, see example below;

Hour	Level	No o Fluct
1	10	0
2	10	0
3	20	1
4	40	0
5	50	0
6	30	1
7	20	0

I am thinking i Logical operations like IF(B3>=B2") it is rising and so on, but it isn't working out the way I thought...
Is there another/better way than boolean/logical?

[Robotacha2010]

examplefluct.xls

If it was me, then I would do the same as what you were attempting. Using logic.

firstly, what counts as a fluctuation? an increase / decrease on a set level? or simply higher or lower than the previous hours level?

assuming you have 1 row per hour for a year, that would equal 8760 rows.

I would have the FLUCT column return an UP , DOWN or SAME value using this formula;

=IF(B3=B2,"SAME",IF(B3>B2,"UP","DOWN"))

Then use a COUNTIF formula to produce the totals/summaries for the period.


I have attached a sample based on 24 hours.

Only issue, would be ROW2 has nothing to compare against as ROW1 is the header row.



This may or may not be what you are after, but I hope it helps.

[beavis]

Attachment 159008

If it was me, then I would do the same as what you were attempting. Using logic.

This may or may not be what you are after, but I hope it helps.

This was exactly what I was looking for, I totally missed the COUNTIF function. Many thanks to you Robotacha!

[beavis]

Ok, now I have had my dinner and the bloodsugar is back on track. :-)

It was almost what I was thinking of, but my problem still exists. I only want to count increase/decrease, i.e peeks and valleys(in some swenglish variant och a proverb)

See attached file, my comments i yellow.

[Robotacha2010]

Hi,

Have a look at the attached. I think this is what you want.

I also added a formula that tells you whether its an increase or decrease against the last hour.

I'm not sure what the conditional formatting will be like as its an .xls file, but I saved it as a .xlsx also.


Hope this helps.

Mike

[beavis]

Problem solved, many thanks once again. :-)