Free Microsoft Excel 2013 Quick Reference

Cumulative frequency formula???

Im trying to show:

Duration Name cumulativeDuration
3 Kev 3
2 Kev 5
4 Phil 4
1 Phil 5
2 Phil 7
3 Jack 3
2 Ross 2
4 Ross 6
5 Ross 11
2 Ross 13

Basically I want a formula (or macro) to get a cumulative amount based on the name given. If you have any suggestions, please help.

Thanks, Kev


Im trying to show:

Duration Name cumulativeDuration
3 Kev 3
2 Kev 5
4 Phil 4
1 Phil 5
2 Phil 7
3 Jack 3
2 Ross 2
4 Ross 6
5 Ross 11
2 Ross 13

Basically I want a formula (or macro) to get a cumulative amount based on the name given. If you have any suggestions, please help.

Thanks, Kev

I have a list of quiz scores from approx. 100 students. There are only 21 possible scores. I have listed the 21 possible scores. In the column next to those scores I want to put the # of students receiving each individual score. (ie. how many received 95, how many 90, and so forth...) I believe the formula I need to use is the frequency formula. However, I have tried my best and still haven't been successful. Does anyone have any suggestions?

Thanks for any help you may be able to provide.

Yesterday there was a post about counting unique values.

The reply had a link to a frequency formula -

http://tinyurl.com/5rloz

The OP never replied back so it's unknown if that formula
worked.

I'm always interested in learning new techniques so I
played around with this formula.

The problem is this formula doesn't work. I copied the
formula to a worksheet (had to remove some junk that got
embedded from the Google archives) but when I tried it I
got an error message that said "not enough arguments for
this function".

The formula doesn't include the Bins_Array argument for
the Frequency function. So I added that argument as an
empty range -

Z1:Z5 with nothing in that range.

According to Excel help, If bins_array contains no values,
FREQUENCY returns the number of elements in data_array.

Well, that did not work. I kept getting results of 0.

Can anyone help me understand that formula and how it
works?

Thank you

I'm trying to make a cumulative total formula for a dial guage i have set up to measure displacement. The gauge only goes up to 50 so it has to be reset when it approaches that value, and sometimes it doesn't work properly and is reset earlier, or just reported as not working. I would like the cumulative total to equal the cumulative total of the last reading if no reading data is added and the instrument to be reset multiple times. The formula is for the cumulative total column. The data I have set up in the sheet as follows:

Date	 Reading	Working? Reset?	Cumulative Total
25/05/2012	2			0
26/05/2012	4			2
27/05/2012	14			12
28/05/2012	27			25
29/05/2012	25			23
30/05/2012	37			35
31/05/2012	40			38
1/06/2012		No		38
2/06/2012		No		38
3/06/2012		broken		38
4/06/2012	0		reset	38
5/06/2012	2			40
6/06/2012	6			44
7/06/2012	23			61
8/06/2012		No		61
9/06/2012	23			61
10/06/2012	35			73
11/06/2012	5		YES	73
12/06/2012	3			71
13/06/2012	4			72
14/06/2012	4			72
15/06/2012		Nope		72
Any help would be tremendously appreciated!

hey there all
i'm using the frequency formula and the large formula in order to find 3 consecutive date in a list of many date

my problem is that the list consist date for number of people and i need the formula to check for the cosecutive dates for each people separately
do anybody have any idea what can i do?

Hi
Is it possible to create a cumulative frequency table with Pivot Tables?
e.g I have a set of data summarised into 5 segments with units #

I can use the pivot table options to produce the 2nd column (% of Total in Column) or 3rd Column ( running total) but I cannot find a way to get the 4th column ( cumulative % running total ?)

data# %total run to ***%
a 40 40% 40 40%
b 20 20% 60 60%
c 30 30% 90 90%
d 5 5% 95 95%
e 5 5% 100 100%

I want to be able to use the data to produce charts without having to copy and paste data to create seperate charts.

I have a frequency formula below, but it's too slow to calculate, and sometimes it just freezes.

Is there another way to make it faster?

Or do I have to create a VBA instead? and will VBA make it faster?

=SUM(IF(FREQUENCY(IF('RAW DATA'!F:F=D4,MATCH('RAW DATA'!B:B,'RAW DATA'!B:B,0)),ROW('RAW DATA'!B:B)-ROW('RAW DATA'!B$3)+1),1))

Thanks!!!

Hi

I am struggling to make a frequency formula that gives me the count of unique entries of one column based on the criteria of another column.
for example the worksheet looks like this
Collummn
A B

1 Jan 05
2 Jan 05
2 Jan 05
1 feb 05
2 jan 05
3 feb 05
4 feb 05
4 feb 05

If I type in Jan 05 the result will be 2
and if I type in feb 05 the result will be 3
I just cant suceed in tying the count to the month chosen.

Thanks for any help in advance.
Seanc

Hi, I need to produce a cumulative frequency chart with the following data. I
am new to this concept on excel so any help will be grately appreciated. here
is the data:

Sentence Length Frequency Cumulative Frequency
1-10 9 9
11-20 12 21
21-30 15 44
31-40 8 49
41-50 1 50

Any help would be appreciated. I hope that i have made myself clear!
Thanks a lot,
Chris Grant

I've a frequency formula that is to slow to calculate,

1. Is there a way to make the calculations faster?
2. And how do I make this formula compatible to excel 2003?

=SUM(IF(FREQUENCY(IF('Raw Data'!F$3:F$65000=D4,MATCH('Raw Data'!B$3:B$65000,'Raw Data'!B$3:B$65000,0)),ROW('Raw Data'!B$3:B$65000)-ROW('Raw Data'!B$3)+1),1))

Thanks!

Dear all,
I have the chart with cumulative% frequency (Y values) vs Bin (X values)
But I have to find X values according to Y values..
For example...
This is the data..

Bin Frequency Cumulative %
50 5 5.00%
100 9 14.00%
150 16 30.00%
200 19 49.00%
250 18 67.00%
300 9 76.00%
350 12 88.00%
400 6 94.00%
450 2 96.00%
500 4 100.00%

Now, I have to find X value when cumulative% is equal to 30%, 40%, and 50%..?
It's OK, if cumulative% value is the same with the questions (30% for example, so the X value is 150). But how if it's 40%... 50%..?

Any help would be appreciated
Thanks and regards
Mut

I have a large amount of data (60k lines) and want to display a cumulative
frequency of the data. Example... chart starts @ 0,0 and end at $1.00 (x) and
100% (y). It will visually show at any given x value (between 0 and 1.00) the
cumulative % of the time that value (and below) comes up. (.50 and below is
40% of the data).

I have played with histograms, but that just shows the frequency of 1 x
value. I need this to be cumulative for that x value and all values lower
than it.

Any ideas?

I am doing a statistics course work and would like to know how to set up a
cumulative frequency curve in Excel. I have tried to insert functions and
using the chart options but I cannot do it in the end.

Please tell me how to do draw a cumulative frequency curve in Excel. Thank
you.

Hi

I have a SUMPRODUCT formula that counts how many people were discharged from my list per month, it looks at column AD which is the discharge date.

=SUMPRODUCT(--(TEXT('Discharge List'!$AD$2:$AD$5000,"mmm/yy")=AT4))

I also have a separate FREQUENCY formula that counts the number of unique numbers (column G) in my live list

=SUM(IF(FREQUENCY('Live List'!G2:G799,'Live List'!G2:G799)>0,1))

What i would like to do is use a frequency formula to count how many unique numbers were discharged per month. Can this be done??

Can I use if statement within the frequency formula?
Thanks,
Ganesh

:coolwink:
To get your results in a set of cells say c1:c5...
...enter your FREQUENCY formula in the FORMULA bar and the hit
CTRL+SHIFT+ENTER
or enclose your FREQUENCY formula in {=FREQUENCY(..:..,..:..)}

so THE RESULTs ARE DISPLAYED IN C1:C5

See attachment!!!

Here is the formula I have so far..
{=SUM(IF((TS!$B$5:$B$103="John, Doe")*(TS!$D$6:$J$104="JobSite"),(TS!$D$5:$J$103)))}

The formula (TS!$D$5:$J$103) is what needs to be the cumulative formula.. It will sum to another sheet..DATA!B5 Then copied down.. I'm keeping hours tracked by Employees and by jobsite and trying to get the hours to accumulate.. to do a "running total"
Never subtract always add... Thanks

below is a formula i have put together with help from this board...I made a slight alteration to it (the F column equation).....and when i do the ctl/alt/enter it comes up as an error display and the "MATCH" word is highlighted as being the problem. I t was working fine until i put in the "F" column equation...........what would make this show up as an error?

=SUM(IF(FREQUENCY(IF('Scaffold List'!$A$4:$A$3822="bd-5",IF('Scaffold List'!$B$4:$B$3822="cui",IF('Scaffold List'!$F$4:$F$3822="Shell",IF('Scaffold List'!$J$4:$J$3822="",IF('Scaffold List'!$D$4:$D$3822"",MATCH("~"&'Scaffold List'!$D$4:$D$3822,'Scaffold List'!$D$4:$D$3822&"",0))))),ROW('Scaffold List'!$D$4:$D$3822)-ROW('Scaffold List'!$D$4)+1),1))

I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:

{SUM(IF(FREQUENCY(IF('Sheet1'!$O$5:$O$315="",IF(Sheet1'!$B$5:$B$315"",MATCH("~"&'Sheet1'!$B$5:$B$315,'Sheet1'!$B$5:$B$315&"",0))),ROW('Sheet1'!$B$5:$B$315)-ROW('Sheet1'!$B$5)+1),1))}

Where Column O has either a date or is blank.
Where Column B has a unique ID number which occurs more than once.

What I need to add is one more criteria from Sheet1,
Where Column P has a unique names which occurs more than once.

I tried two changes:
1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!$P$5:$P$315=A$8,IF('Sheet1'!$O$5:$O$315="", ... Which I couldn't get to take; and
2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!$P$5:$P$315=A$8,'Sheet1'!$O$5:$O$315=""), ... Which took but gives me a #VALUE error.

Please help, thank you,

Rick

I have this formula listed below which works, but I want to revise it to read another column.

=SUM(IF(FREQUENCY(IF(('Scaffold List'!$A$4:$A$4001="bd-5")*('Scaffold List'!$B$4:$B$4001="cui"),IF('Scaffold List'!$D$4:$D$4001"",MATCH("~"&'Scaffold List'!$D$4:$D$4001,'Scaffold List'!$D$4:$D$4001&"",0))),ROW('Scaffold List'!$D$4:$D$4001)-ROW('Scaffold List'!$D$4)+1),1))

The formula above does the following:

Look in column A for any "BD-5" listing....then look in column B for any "CUI" listing...once that count is made, then look in column D and only count the unique entries (no repeats to be counted).......whatever that result is, is the answer im looking for. And the formula works perfect....

But i want it to extend my filtering even more, and include if no value in column J, then carry on with the column D formula of counting unique entries, and then that result will be my new answer. (if column J does have a value,, a date in this case, then dont count it......ONLY count if blank)

so basically wedging the column J formula in there before the existing formula kicks into the column D formulas.

man, i hope i am clear in what im looking to do here.

can someone please help me out?

Why/how would such a thing occur?

Age today Bins Count Formula under Count:
65 70 1

I am looking for a formula that will allow me to run a cumulative total of inventory. For example in cell A1 I will enter the number of widgets purchased that day. In cell B1 I want to record the number of widgets previously ordered plus the new widgets so I can track the daily order and the total for the year. So Cell A1 will show the current amount of widgets ordered and cell B1 will show the total widgets ordered for the year.

If I ordered 1 widget today A1 would show 1 and B1 would show 1.

If I ordered 3 widgets tomorrow A1 would show 3 and B1 would show 4 (as the cumulative total.

from a humble widget counter

hi,
im trying to figure out the frequency of a sample of students final marks
and this is the formula im using '=FREQUENCY(FinalMarks,B11:B12)' but as i go
from B11:B12 to B12:B13 it accumulates the score instead of just counting the
frequency of B12:B13. how do i adjust the formula so it doesn't accumulate
thanks

Can some one please hlep me to right a formula to find the amount of times a
certain numbers shows up with in a series of numbers.

For example if i have
(column A)
2 (row 1)
3
4
3
2
2
4 (row 7)

and i wanted to write a formula to show me how many times the numbers 2 and
3 appear with in that series how do i write it.

i have been playing with =frequency(A1:A7,2) just to find one number
thinking that would give me the number of times 2 shows up but that gives me
the number of times a number a number 2 or less then 2 shows up. I have been
trying for 2 hours now to get this to work. someone please help.

thanks

rich