0

I am using excel and vba to find the asymmetry of a gaussian curve. My data is stored in a column. In order to find the asymmetry I need to find the maximum (peak height) value and then find the values close to 1/10 of the max on either side of the curve. To do this I iterate once to find the max then iterate in two halves to find and set the values to a variable (closest101) corresponding to 1/10 the max. I am comparing the difference of the (1/10)max with the value in the cell and when a smaller difference is obtained, the value in the cell is the new closest match. I am not using Vlookup or Match function because my data is very noisy. The code for that is below:

'low is 1/10 max
For Each cell In halfRng
cell.Select
Debug.Print Abs(low - ActiveCell.Value)
    If Abs(fifty - ActiveCell.Value) <= difference50 Then
        difference50 = Abs(fifty - ActiveCell.Value)
        closest501 = ws.Range("C" & ActiveCell.Row).Value
    ElseIf Abs(low - ActiveCell.Value) <= difference10 Then
        difference10 = Abs(low - ActiveCell.Value)
        closest101 = ws.Range("C" & ActiveCell.Row).Value
        Debug.Print "new lowest difference  " & difference10
    End If
Next cell

I am also looking for the value at half the peak height, but that works fine with the same logic.

The problem that I am having is that the "logic" fails for values that should work. For example, a difference of 0.06 is less than the previous smallest difference which was 0.3. In this case, the ElseIf would be true and the new difference10 would be 0.06 but it behaves as if that condition is false. Any suggestions?

Edit: Here is some sample data:

difference10 = 10000 'initial value
Low = 1
Cell.value(1) = 0.7
Abs(low - cell.value(1)) = 0.3 <= 1000
So difference10 =0.3
...
Cell.value(10)  = 0.9
Abs(low - cell.value(10)) = 0.1 <= 0.3 'fails here
difference10 =0.3 'remains unchanged
Astro
  • 13
  • 1
  • 5
  • Sounds like you need to flip your logic around - first check the smallest difference, then the next smallest. – BigBen Jul 16 '20 at 03:29
  • Are you able to post a sample set of values that cause the logic to fail? `E.g., low = 50, ActiveCell.Value = 40`, etc. – BDra Jul 16 '20 at 03:31
  • A [mcve] would help, although I think that @BigBen identified the core issue. Also, you could probably benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). Your code is less readable and less efficient than it could be. – John Coleman Jul 16 '20 at 11:21

0 Answers0