How to add the counts of a series?

[deuce]

Hello, I am trying to obtain the correct results using the exact technique as stated below.

From J24 downwards I have numbered values going from max to min, from K24 I have the number of counts for each value in J24.

The Method is :

1) Sum up the total number of counts from J24. Output this result in O11.

2) Take 68% or 70% of this total number of counts and output this count result to P11, the percent value is typed into Q11 and this percent value value can change.

3) Identify the Modal Value cell address which is already calculated by a formula in cell E4.

4) Add The counts of the 2 numbers above and 2 below the modal value, meaning add the counts of the 2 numbers above the modal value then add the counts of the 2 numbers below the modal value.

5) The counts of the pair of numbers with the greater number should be added to the counts of the modal value.

We continue this procedure until the total number of counts reaches the percentage value stated in point number 2.

Here is an example with the expected result :

# C
1083 1
1082 2
1081 2
1080 4
1079 4
1078 4
1077 4
1076 5
1075 7
1074 9
1073 11
1072 10
1071 10
1070 9
1069 9
1068 8
1067 6
1066 6
1065 3
1064 3
1063 3
1062 2
1061 2
1060 2
1059 2
1058 2
1048 1


Total Counts = 131

70% of total counts = 91.7

Counts of the modal Value = 11

Pair counts : 11 + 20 + 18 + 16 + 14 + 9 + 6 = 94

Upper limit number: 1080

Lower limit number: 1074

Hope someone can help me out in this.

Thanks.

 

[GlennUK]

Your data starts in A2 and B2, and goes downwards. Copy the formulas I've given for C2, D2 and E2, and copy down.

In a blank area, for me column L, I typed some formulas , just doing the ones in L2 and L4 first.

In another blank area ( G:I for me ), I typed the numbers 1 to 13, starting in G3 and going downwards. In H2 I typed a formula, and in H3 and I3 2 more formulas, and copied them down.

Then I did the rest of the formulas in column L

That's it.

 

[deuce]

i don't understand it

can you tell me in which column to copy which formulas and which cells are to have what data?

formula entry looks pretty puzzling

 

[deuce]

i got the formula entry for c,d,e columns correct but what after that?

 

[GlennUK]

Do cells L2 and L4:-
L2 =K2*SUM(B:B)
L4 =MATCH(MAX(B:B),B:B,0)


Do cells G3 downwards ( no formulas, just numbers ) to cell G15

Do cell H2. :- =INDEX(B:B,L4)

Do cells H3 and I3,
H3 =INDEX($C$2:$C$28,MATCH(G3,$D$2:$D$28,0))
I3 =SUM($H$2:H3)

and copy down as far as H15:I15.

Do the remaining formulas for column L, some of which are array formulas ( entered using Ctrl-Shift-Enter instead of Enter ):
L6 {=MIN(IF($E$2:$E$28<=MATCH(L2,$I$3:$I$15),ROW($E$2:$E$28),9999))}
L7 =COUNTIF($E$2:$E$28,"<="&MATCH(L2,$I$3:$I$15))
L8 =SUM(OFFSET($B$1,L6-1,0,L7,1))
L9 =IF(MATCH(MATCH(L2,$I$3:$I$15)+1,D2:D28,0)>MATCH(L2,$I$3:$I$15),0,-1)
L11 =SUM(OFFSET($B$1,L6+L9-1,0,L7+1,1))
L12 =SUM(OFFSET($B$1,L6+L9*2-1,0,L7+2,1))
L15 =IF(L11>L2,INDEX(A1:A28,L6+L9),INDEX(A1:A28,L6+L9*2))
L16 =IF(L11>L2,INDEX(A1:A28,L6+L9+L7),INDEX(A1:A28,L6+L9*2+L7+1))

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

 

[deuce]

hi, I get #N/A results in L column, L9, L11, L12, L15, L16.

what to do?

 

[deuce]

I observed that in the cell that contains the random numbers if i type in the numbers 1-13 in G column I get no results but if i type in some random numbers i get results but those are incorrect also.

so the problem being caused is by the reference to G column and not knowing what to type into that column so that I get the correct results for the upper and lower limits.


can any suggestion be made because i still have not gotten my solution to the problem.

 

[GlennUK]

Use Excel Jeanie to show:

entire range of input data that exists in columns A and B.

a few rows of the columns C,D, & E ( say 1 to 6 ).

G1:I6 layout and formulas

K2:L16 layout and formulas.