Value Error

I get a #value error for my UDF which I have agonised over for hours/days. The code is:

Please Login or Register  to view this content.

The X data is:

98
98.02
98.04
98.06
98.08
98.1
98.12
98.14
98.16
98.18
98.2
98.22
98.24
98.26
98.28
98.3
98.32
98.34
98.36
98.38
98.4
98.42
98.44
98.46
98.48
98.5
98.52
98.54
98.56
98.58
98.6
98.62
98.64
98.66
98.68
98.7
98.72
98.74
98.76
98.78
98.8
98.82
98.84
98.86
98.88
98.9
98.92
98.94
98.96
98.98
99
99.02
99.04
99.06
99.08
99.1
99.12
99.14
99.16
99.18
99.2
99.22
99.24
99.26
99.28
99.3
99.32
99.34
99.36
99.38
99.4
99.42
99.44
99.46
99.48
99.5
99.52
99.54
99.56
99.58
99.6
99.62
99.64
99.66
99.68
99.7
99.72
99.74
99.76
99.78
99.8
99.82
99.84
99.86
99.88
99.9
99.92
99.94
99.96
99.98
100
100.02
100.04
100.06
100.08
100.1
100.12
100.14
100.16
100.18
100.2
100.22
100.24
100.26
100.28
100.3

... and the Y data selected is:
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
2
4
5
11
22
30
40
49
58
74
89
89
90
72
64
38
18
3
3
0
0
1
0
0
1

If anyone can help me understand why I keep getting this error I will be very grateful!!!


Thanks

Thanks Whizbang for your suggestions.

About 'I'...
Ideally I should be set to be the correct index for when Y is a maximum.

Because...

The program looks at a generally bell shaped curve and find the points the X values that correspond to 10% of the y maximum.
1. So initially Y max is found. Then we find 10% of Y max.
2. Then we start to move along the x axis and examine the y value. When the Y value exceeds 10% of the max then we interpolate to find the corresponding X value where this occurs.
3. Then (what my code was doing) was continuing on from the current index point and looking for the next y value that is less than 10% of y max. Then it interpolates to find the corresponding x value.
It would be best if after 2 has finished we could move the index to the corresponding point where Y max is and continue to iterate from their to complete step 3.

What do you think? Any ideas?

Thank you tlafferty. Appreciate your help.

I'll look in to that now. I wonder why it wouldn't complete the last part. Maybe because their is an issue with XTenth2?

First, it appears that posts were made while I was editing my code. So, please re-read my posted code to ensure you see my edited version.

I am not quite following you. Please give my code a try (with and without the second I = 1), and let me know your response.

Thanks Marcol. That is another problem with the code although it is not causing the problem here.

Thanks

Thanks Whizbag I understand your corrections however 'I' must not be reset back to 1

I've run in the debug mode and I see the problem.

As it iterates through in the second Do Loop, when I = I + 1 is > 108 it cuts out and goes out of context. I cant understand why??

Since there are 115 entries in each column. Any ideas???

To account for the fact that there may never be an instance where Y(I) <= YTenth, you could do this:

Please Login or Register  to view this content.

All unhandled errors will cause your UDF to fail and return #VALUE!

Seems others couldn't get past the lines I posted, so it would seem to be a good idea to at least start there.

'I' is at 108. At 'I' = 117 one would expect the IF statement to become true and then XTenth2 to be set.

But upon the next iteration after 108, everything goes out of context.

Please see attached.

Edited my last post to fix an error.

Originally Posted by Whizbang

Edited my last post to fix an error.

Thanks Whizbang I really appreciate that. I like your style. However, I am reluctant to use one for loop for both operations in case there are any discrepancies in the peak and it has some fluctuations.

I still can't understand why I = I + 1 would set itself back to 1 or go out of context. That doesn't make any sense to me?

Originally Posted by snb

What strikes me as inexplicable is that you persist in not posting a sample workbook....

My apologies snb.

I've attached a sample workbook.

As a side issues. I keep getting a circular reference message...

Thanks snb. Really appreciate your help there. Its good having a fresh pair of eyes look at your code with new ideas.

One other point.

In the case that I have data that does not have a peak (and I don't mean small fluctuations).

How could I go about in the function checking to see whether even a peak existed???

I have attached some sample data where it does not give a peak. Any ideas?

Thanks again.