Formula Help

[Lady Excel]

I have a lot of ongoing data that if plotted would give a sine curve with
peaks between 0 and 10. In this case I need a formula that will add the
amplitude of the peak on its way down but ignores the info on its way up. Any
ideas?

example: 0 5 10 6 2 8 10 7 5 should give a sum of (10-2)+(10-5)=13

 

[Bernard Liengme]

Here is a UDF that seem to work. I make a couple to data set to test it
You need to add a large value at the end of the actual data and in include
this in the range when you call the function with =MYSINE(range)

Function mysine(myrange)
mysum = 0
mycount = myrange.Count
For j = 1 To mycount
If myrange(j) = WorksheetFunction.Max(myrange(j - 1), myrange(j),
myrange(j + 1)) Then
mymax = myrange(j)
End If
If myrange(j) = WorksheetFunction.Min(myrange(j - 1), myrange(j),
myrange(j + 1)) Then
mymin = myrange(j)
mysum = mysum + (mymax - mymin)
End If
Next j
mysine = mysum
End Function

New to VBA?
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"
http://www.contextures.com:80/xlvba01.html

best wishes