Heres the first worksheet that needs to be turned into a culmulative chart Now I
ID: 3673319 • Letter: H
Question
Heres the first worksheet that needs to be turned into a culmulative chart
Now I need to do this...
Can someone tell me what formula to use? Your help is very much appreaciated.
D E 2 2015-01-01 Birth Rabbit r1 3 2015-01-01 Birth Rabbit r2 4 2015-01-01 Birth Wolf w1 5 2015-01-01 Birth Wolf w2 6 2015-01-03 Birth Rabbit r3 rl 7 2015-01-05 Birth Wolf w3 w1 8 2015-01-11 Death Rabbit r2 w2 2015-01-12 Death Rabbit r1 W2 10 2015-02-08 Birth Rabbit r4 r 11 2015-02-10 Birth Rabbit r5 r4 12 2015-02-11 Birth Rabbit r6 r5 2015-02-21 Death Rabbit r6 w1 14 2015-03-07 Birth Wolf w4 w1 15 2015-03-11 Birth Rabbit r7 r3 16 2015-03-14 Birth Rabbit r8 r5 17 2015-03-15 Birth Wolf w5 w4 18 2015-03-17 Birth Rabbit r9 7 19 2015-03-20 Death Rabbit r5 20 2015-03-24 Birth Rabbit r10 r4 21 2015-04-06 Birth Rabbit r1l r8 22 2015-04-20 Birth Rabbit r12 r3 23 2015-04-25 Birth Rabbit r13 r4 24 2015-04-28 Birth Rabbit r14 r7 25 2015-04-29 Birth Rabbit r15 r13 26 2015-05-03 Birth Rabbit r16 r15 27 2015-05-07 Birth Rabbit r17 r8 28 2015-05-11 Birth Wolf w6 w1 29 2015-05-14 Birth Wolf w7 w4 30 2015-05-15 Birth Wolf w8 w7 1 7Explanation / Answer
Hi,
The formula is the following for rabbits
=SUM(COUNTIFS(C2,"rabbit",B2,"birth")-COUNTIFS(C2,"rabbit",B2,"death"),F1)
The formula is the following for wolves
=SUM(COUNTIFS(C2,"wolf",B2,"birth")-COUNTIFS(C2,"rabbit",B2,"death"),G1)
Note: Please type these formula in F2 and G2 respectively ( more specifically second row of which ever column you have chosen to store results)
Explanation for Formula 1(rabbits):
COUNIFS is an inbuilt function that take more than one pairs of range and text to be searched.Here I want to search for "rabbit" in Cell C2 and search for "birth" in B2 simultaneously.
In the second usage of COUNTIFS, I want to search for "rabbit" in Cell C2 and search for "death" in B2 simultaneously and I am subtracting dead rabbits from alive ones to find the current population.
Note: The order of pairs in COUNTIFS doesn't matter.We can also write COUNTIFS(B2,"birth",C2,"rabbit") instead of COUNTIFS(C2,"rabbit",B2,"birth").
SUM is a inbuilt formula in excel that takes arguments that are needed to add together.I have taken F1 as second argument because we want cumulative frequency,right.So we need to add the frequency of upper row to make it cumulative.As I am supposing my results are getting stored in F column.So I have taken F1 ,you can take G1,H1 etc depending on in which column you want to store the results.