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]

I don't see how the "+ 6" in your pair counts is included ... unless there's some logic that you haven't mentioned ( like not always including pairs, but single numbers at some time ).

I also don't see how your pairs match the limits 1080 and 1074 ... can you explain that?

 

[deuce]

hi thanks for your response and sorry for a late one from me.

The total number of counts 94 in this case, if you minus the 6 from 94 you get 88 which is less than the 91 needed however when 6 is included we get an acceptable result.

An example : It's like when going shopping, one cannot pay less at the cash counter it's better to pay a higher denomination to the cashier and get back a little bit of change in the pocket.

The example and expected results that I have posted are correct.

hope this helps to create a formula in excel that gives the correct results.

thanks and regards,
deuce.

 

[GlennUK]

I know that if you minus the 6 you get 88 etc ... it's not a pair then, even though you said that the logic was to assign pairs. So is the rule that single numbers can be assigned for the last number then? And why do you not use 5? ... see below
11+ (10+10) + (9+9) + (9+7) + (8+6) + (6+3) + 5

And you still haven't answered this:

I also don't see how your pairs match the limits 1080 and 1074 ... can you explain that?

???

 

[deuce]

hi thanks for the response, i was not able to respond yesterday because of connection problems.

Basically it is all explained in the points that I have made. remember that we are adding 7 pairs in the example and how far up we go or how far down in adding depends on the pair with the higher count which is added to the counts of the modal value (point 5).

Thanks and regards.
deuce

 

[GlennUK]

No, my queries are not explained in the points that you have already made ( at least, I don't think so ).

Show the 7 pairs in the example ... I cannot see which 7 you mean.

And still you have not answered this:

I also don't see how your pairs match the limits 1080 and 1074 ... can you explain that?

... so why aren't you explaining this? I've asked this twice before.

 

[deuce]

Hi, I have posted another example with a numbered list so we can know which series is being referred to :

1 2165 1
2 2164 2
3 2163 2
4 2162 4
5 2161 4
6 2160 4
7 2159 4
8 2158 5
9 2157 7
10 2156 9
11 2155 11
12 2154 10
13 2153 10
14 2152 9
15 2151 9
16 2150 8
17 2149 6
18 2148 6
19 2147 3
20 2146 3
21 2145 3
22 2144 2
23 2143 2
24 2142 2
25 2141 2
26 2140 2
27 2130 1

Upper limit number: 2159

Lower limit number: 2148

Mode: 2155.

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

We have identified the mode as being #11, 2155. We start adding counts from the first pair of numbers below the mode which are from (2153,2154), then repeat the one upper pair above the mode which are (2156,2157), the first pair count below the mode totals 20, above the mode equals 16, hence we add this total 20 to 11 counts of the mode and move the range down and so forth.

You may be referring to the range looking lop-sided, here it is.

Pairs :
2158,2159
2156,2157
2155 (mode)
2154,2153
2152,2151
2150,2149
2148.

You may be questioning why not use the counts from 2160 and 2161 then look to do the same with 2147, 2148, well it would exceed the threshold limit of 91 so we had to count the so called tipping factor which would have tipped way past the counts set in the threshold limit of 91. hence would look at the first line above and below the last added pairs and use the one with the greater counts in this case 2148.

Hope this explains. if you have any other questions then please do ask.

 

[GlennUK]

So actually in your original example, the range was 1066 to 1077, not 1074 to 1080 as you stated.

And now you state that the last number need not be a pair ... even though you only mentioned pairs before. Well, now that I have that information I can start trying to get a solution ( I had a solution for pairs only ).

But your final paragraph doesn't make sense:

You may be questioning why not use the counts from 2160 and 2161 then look to do the same with 2147, 2148, well it would exceed the threshold limit of 91 so we had to count the so called tipping factor which would have tipped way past the counts set in the threshold limit of 91. hence would look at the first line above and below the last added pairs and use the one with the greater counts in this case 2148.

... this states that you don't want a final pair, as the tipping factor would be way past the required limit ... but then you say that you want the highest of the 2 single numbers above and below, even though the lower one would have still made the total above the limit ( the 4 gives a total of 92, which is still above 91.7 ) ... can you explain why the 4 is not chosen? ( just curious is all, I'll try to follow your requirements anyway )

 

[GlennUK]

Look at these blocks ( truncated, but copy down where appropriate ):

Excel Workbook
	A	B	C	D	E
1	*	*	pair sums	rank pair	copy of ranking
2	2165	1	*	*	12
3	2164	2	3	12	12
4	2163	2	*	*	8
5	2162	4	6	8	8
6	2161	4	*	*	7
7	2160	4	8	7	7
Sheet

Excel Workbook
	G	H	I
1	*	sorted sums	cum sum
2	*	11	*
3	1	20	31
4	2	18	49
5	3	16	65
6	4	14	79
Sheet

Excel Workbook
	K	L
2	70%	91.7
3	*	*
4	pos of max	12
5	*	*
6	start point	8
7	length	11
8	test amount	88
9	start offset	0
10	*	*
11	add 1 cell	94
12	add 2 cells	97
13	*	*
14	*	*
15	upper limit	2159
16	lower limit	2148
Sheet

... does that do what you want?

 

[deuce]

hi, basically when you add a pair of counts, the criteria is like comparing the pairs above and below the last added pair and asking the question, is this pair of counts greater than the corresponding pair, the counts of the pair with the higher number are added to the counts of the mode.

Basically we have 3.5 pairs added below and 2 pairs above the mode.

If we were to follow the same principle given in the pair technique outlined we would end up with 8 counts total for (2160,2161) and 9 counts total for the pair (2148,2147).

Basically to explain further we devised a way to calculate the 1 stdev however when stdev is calculated for the range we get an equal result of which the mode becomes the average which is incorrect but with the method we are using we get a true reflection of the actual symmetry meaning it looks skewed more to the right rather than the left, the technique outlined tends to fix that problem but it needs to be done up correctly.

I see your formulas but i see a whole long list of them.

how?, where? do I start entering all these formulas or you have given more than 1 formula to achieve the same results. meaning which of them is correct?

please guide me in entering those formulas.

thanks and regards,
deuce.