Have pulled out the last hair on my head

[jrb]

I posted this same problem in the general forum a few weeks ago. Thoug
I learned some new functions from the advice, I can't get it to solv
my problem. I am wondering if I need a VBA solution.

Each day I get a column of data, that will be anywhere from 14 -50
rows in length. (depending on the number of days being analyzed) t
make it simple if we just use this list as an example.

4
5
3
5
9
7
4
3

I need to sum the central number (9) with the sum of the cells abov
and below the central number until the total sum equals X

1) X = 70% of the combined sum - so in this case 53*.7 = 37.1 .

2) The central number is based on the largest number in the list. I
there are multiple matches, it is closest to the physical middle of th
list. I think I have worked this out to where the proper number i
selected and I made it so a 1 is placed in the column next to th
number.

3) The summed number does not have to be exact, just as close to th
37.1 as possible in other words + or - 1 row

4) The numbers do not have to be added in pairs. Just 1 above 1 belo
alternating one after another. In other words you add the 5 from abov
to the central (9) then add the 7 from below until it approximatel
equals 37.1 . To take it one step further if it were to be don
correctly after adding the above and below numbers the next number tha
would be added would be from the side that had the largest number th
last time.


In essence this is a pseudo 1 std deviation. Trying to find 70% of th
distribution around the central numbers. Creating somewhat of a bel
curve around the central number

 

[Kevin Beckham]

What do you want to know?
How many rows? Which rows?

How about a set of numbers that actually sum to 53

Kevin Beckham

 

[Kevin Beckham]

The following code may give you some ideas

Function PseudoStdDev(rng As Range) As Variant
Dim rngStart As Range
Dim maxVal As Long
Dim rngSum As Long
Dim midPlus As Long
Dim midMinus As Long
Dim midVal As Long
Dim midSum As Long
Dim result(1 To 3) As Long

maxVal = Application.WorksheetFunction.Max(rng)
rngSum = Application.WorksheetFunction.Sum(rng)

'find max value nearest mid-point
midMinus = rng.Rows.Count \ 2
midPlus = midMinus + 1
Set rngStart = rng.Resize(1, 1)

Do
If rngStart.Offset(midMinus, 0).Value = maxVal Then
midVal = midMinus
Exit Do
ElseIf rngStart.Offset(midPlus, 0).Value = maxVal
Then
midVal = midPlus
Exit Do
End If
midMinus = midMinus - 1
midPlus = midPlus + 1
Loop

'now start from the mid-point
midSum = maxVal
midMinus = midVal
midPlus = midVal

Do While (Abs(midSum - 0.7 * rngSum) > rngStart.Offset
(midMinus - 1, 0).Value _
Or Abs(midSum - 0.7 * rngSum) > rngStart.Offset
(midPlus + 1, 0).Value) _
And (midPlus <= rng.Rows.Count) And (midMinus >= 0)
If rngStart.Offset(midMinus - 1, 0).Value >
rngStart.Offset(midPlus + 1, 0) Then
midMinus = midMinus - 1
midSum = midSum + rngStart.Offset(midMinus,
0).Value
Else
midPlus = midPlus + 1
midSum = midSum + rngStart.Offset(midPlus,
0).Value
End If
Loop


result(1) = rngStart.Offset(midVal, 0).Row
result(2) = rngStart.Offset(midMinus, 0).Row
result(3) = rngStart.Offset(midPlus, 0).Row
Set rngStart = Nothing

PseudoStdDev = result
End Function

Kevin Beckham

 

[jrb]

Kevin,

Thank you for your response and efforts, I will play with your code an
see if I can get it to work. Again thanks

 

[jrb]

Kevin,

After playing with this a bit, I guess I am not smart enough to figur
out how to get the 3 results that you have coded in. Do I need to se
the sheet up a certain way? Thanks fr your efforts and time