Cross ref Target number in a list for 98%

[slamanager]

Hi,

I have a list of data returns the numbers in that range fall between 0 to 100 a lot of the numbers fall in the low part of this range eg. 9.
I need to work out the target number of this range at 98%.

So 98% of 2015 = 1974 the target value would be 25 because that is the sum value of 1974 is there a formula that can give me the target value based on a list or pivot that list.

counts of data returns expressed in a table :
Number - Ret Value
1 1
8 15
9 1843
10 47
11 17
12 6
13 6
14 5
15 6
16 4
17 1
18 2
19 5
20 4
22 3
23 3
24 1
25 5
26 1
27 2
28 5
29 1
31 1
32 1
33 1
34 1
35 1
36 3
38 1
40 1
41 2
43 2
47 1
48 1
51 3
53 2
56 1
58 2
62 2
64 1
69 1
80 1
81 1
84 1
100 1
Grand Total 2015

 

[CastroTheCat]

There's probably a better way, but I would:

1) add a column that gives a TRUE/FALSE response to see if the running total of your count falls within the 98% rule.

If this were in column C, C1 would read:

=SUM($B$1:B1)>(SUM(B:B)*.98) 'drag this down the list of values

2) Mirror the values column in D

D1 would read:

=A1 'drag this down the list of values



3) Then you'd use a vlookup to find the first value that returns a FALSE (ie falls outside of you 98% rule)

=VLOOKUP(FALSE,C1:D100,2,1)




You could add extra cells to change the percentage of returns you want to include (if you needed that sort of flexibilty). You'd then just change the formula in C1 to:

=SUM($B$1:B1)>(SUM(B:B)*[Cell reference])

You could also add a cell to show your target value:

=ROUNDDOWN(SUM(B:B)*.98,0)





"I'm not a genius. I'm a chicken"
Pinky PIe

 

[slamanager]

I thought of this i was really looking for a formula to tell me it was 25 rather than use a helper column but i think thats the only way.