fishoutofwater
New Member
- Joined
- Jul 20, 2011
- Messages
- 38
Hey gurus and geniuses... much obliged if you can help me on this stumper... it should be easy, but I am cracking my head against the keyboard trying to crack it, to no avail... first, I'll show you the data...
<tbody>
</tbody>
OK, let's call the left column "score" and the right column "value" and the highest "score" the "peak" (In the example the "peak" is the 6 in row 7 . What I want to do is figure out the values in the 2nd column associated with the 70% interval around the peak. In other words, the logic looks like this...
STEP 1: Figure out the sum of Scores {ie. sum of column A = 30}
STEP 2: Figure out 70% of sum of Scores {ie. (=70%*sum(colA)) = 21}
STEP 3: Here is where it gets tricky....
I have tried adding helper columns, and I have smoke coming out of my ears from my brain cells catching fire, and I just have to admit defeat... I have no clue how to approach this...
| 0 | 50 |
| 2 | 60 |
| 5 | 70 |
| 4 | 80 |
| 3 | 90 |
| 4 | 100 |
| 6 | 110 |
| 2 | 120 |
| 3 | 130 |
| 1 | 140 |
<tbody>
</tbody>
OK, let's call the left column "score" and the right column "value" and the highest "score" the "peak" (In the example the "peak" is the 6 in row 7 . What I want to do is figure out the values in the 2nd column associated with the 70% interval around the peak. In other words, the logic looks like this...
STEP 1: Figure out the sum of Scores {ie. sum of column A = 30}
STEP 2: Figure out 70% of sum of Scores {ie. (=70%*sum(colA)) = 21}
STEP 3: Here is where it gets tricky....
(a) START AT THE PEAK... We have 6 (the green value) so add it TEST (the first time the TEST=PEAK)
(b) CHECK TO SEE IF WE have exceeded the STEP 2 Value, if not continue... if so END
CHECK the value one above and one below the current included Range (ie. compare the red numbers the 4 and the 2)
ADD the bigger number to our TEST (so 6+4) = 10
(b) CHECK TO SEE IF WE have exceeded the STEP 2 Value, if not continue... if so END
CHECK the value one above and one below the current included Range (ie. compare the red numbers the 4 and the 2)
ADD the bigger number to our TEST (so 6+4) = 10
(c) If our TOTAL TEST SUM is still less than the 70%, check the 2 new numbers at the edge of the range. Now it will be the red 2 and the purple 3. Add the bigger number (the 3) to our TEST value, and check if our current total test (now at 13) is more than 70%, if not, continue.
(d) Compare the two numbers at the edge of the range (Now that is the red 2 and the orange 4) Add the bigger number to our test. That would be the 4. Now we are up to 17. 17 is still less than 21 (the 70%) so we do it again...
(e) Test the red 2 (at the lower edge of our range still) and the pink 5. The pink 5 is bigger, so we add it to our test. Now our test is 22. Since 22 is bigger than the 70% value (21) we stop and we have identified a range. This range is marked by the Red numbers in column 2.
STEP 4: THE REAL GOAL!!!! I want to know the top of the range (in column 2) and the bottom of the range (in column 2)... so my answer would be in two cells, the first cell = 70; the 2nd cell = 110, representing that 70% of the distribution around the peak value in column 1 is inside the range of 70 to 110. (d) Compare the two numbers at the edge of the range (Now that is the red 2 and the orange 4) Add the bigger number to our test. That would be the 4. Now we are up to 17. 17 is still less than 21 (the 70%) so we do it again...
(e) Test the red 2 (at the lower edge of our range still) and the pink 5. The pink 5 is bigger, so we add it to our test. Now our test is 22. Since 22 is bigger than the 70% value (21) we stop and we have identified a range. This range is marked by the Red numbers in column 2.
I have tried adding helper columns, and I have smoke coming out of my ears from my brain cells catching fire, and I just have to admit defeat... I have no clue how to approach this...
