Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoTabulate
Hi All,
I would like to do a auto tabulate formula for this problem, rather than i go calucate from time to time. Let's say if i have a table like this: No Game Type Hours --- ------ ----- ------ 1 swim outdoor 2 2 jog indoor 1.5 3 swim outdoor 1 4 bball both 3 5 vball indoor 1.5 6 vball indoor 1 7 tennis outdoor 2 With the above table, the excel will auto give me a drop down (i dont know what does this called), it will help me to group under 1) jog, swim bball, vball & tennis. 2) indoor,outdoor & both. 3) 0.25,1,3 & 1.5 __________________________________________________ ______________ Now i trying to create a calculation by having this: Total | Swim | Jog | BBall | vBall | Tennis count 7 2 1 1 2 1 Hours 12 3 1.5 3 2.5 2 If i using formula inside the fx=count for Total, it will be simple. But how do i calculate for other like Swim, Jog, etc THanks~ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoTabulate
=counta(b2:b9999)
will give you the total number of cells in B2:B9999 that have anything in it. =sum(d2:d9999) will sum the total hours in D2:D9999 =countif(b2:b9999,"swim") will count the number of cells that are equal to "swim" =sumif(b2:b9999,"swim",d2:d9999) will sum the values in D2:D9999 where the value in column B (same row) is equal to "swim" You may want to learn more about Data|Pivottables (xl2003 menu system). It could make this kind of summary report lots easier. kyoshirou wrote: Hi All, I would like to do a auto tabulate formula for this problem, rather than i go calucate from time to time. Let's say if i have a table like this: No Game Type Hours --- ------ ----- ------ 1 swim outdoor 2 2 jog indoor 1.5 3 swim outdoor 1 4 bball both 3 5 vball indoor 1.5 6 vball indoor 1 7 tennis outdoor 2 With the above table, the excel will auto give me a drop down (i dont know what does this called), it will help me to group under 1) jog, swim bball, vball & tennis. 2) indoor,outdoor & both. 3) 0.25,1,3 & 1.5 __________________________________________________ ______________ Now i trying to create a calculation by having this: Total | Swim | Jog | BBall | vBall | Tennis count 7 2 1 1 2 1 Hours 12 3 1.5 3 2.5 2 If i using formula inside the fx=count for Total, it will be simple. But how do i calculate for other like Swim, Jog, etc THanks~ -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoTabulate
where can i find more on Data|Pivottables (xl2003 menu system)?
between if i have data like this: ID No Game Type Hours --- ------ ----- ------ S0910 1 swim outdoor 2 S0911 2 jog indoor 1.5 S0912 3 swim outdoor 1 DR1810 4 bball both 3 DR1811 5 vball indoor 1.5 DX0010 6 vball indoor 1 DX2000 7 tennis outdoor 2 Now can i do a count by ID (first 4 letters) Mean by S091 will give 3, DR18 will give 2 and DX00 will give 1 and DX20 will give 1. Can this be dong? "Dave Peterson" wrote: =counta(b2:b9999) will give you the total number of cells in B2:B9999 that have anything in it. =sum(d2:d9999) will sum the total hours in D2:D9999 =countif(b2:b9999,"swim") will count the number of cells that are equal to "swim" =sumif(b2:b9999,"swim",d2:d9999) will sum the values in D2:D9999 where the value in column B (same row) is equal to "swim" You may want to learn more about Data|Pivottables (xl2003 menu system). It could make this kind of summary report lots easier. kyoshirou wrote: Hi All, I would like to do a auto tabulate formula for this problem, rather than i go calucate from time to time. Let's say if i have a table like this: No Game Type Hours --- ------ ----- ------ 1 swim outdoor 2 2 jog indoor 1.5 3 swim outdoor 1 4 bball both 3 5 vball indoor 1.5 6 vball indoor 1 7 tennis outdoor 2 With the above table, the excel will auto give me a drop down (i dont know what does this called), it will help me to group under 1) jog, swim bball, vball & tennis. 2) indoor,outdoor & both. 3) 0.25,1,3 & 1.5 __________________________________________________ ______________ Now i trying to create a calculation by having this: Total | Swim | Jog | BBall | vBall | Tennis count 7 2 1 1 2 1 Hours 12 3 1.5 3 2.5 2 If i using formula inside the fx=count for Total, it will be simple. But how do i calculate for other like Swim, Jog, etc THanks~ -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoTabulate
I'd add a new column based on the ID column. I'd use formulas like:
=left(a2,4) (and drag down) Then give that column a nice header so that I could use it in the pivottable. Just drag that header into the data field and you should see the "count of IDAbbrev". kyoshirou wrote: where can i find more on Data|Pivottables (xl2003 menu system)? between if i have data like this: ID No Game Type Hours --- ------ ----- ------ S0910 1 swim outdoor 2 S0911 2 jog indoor 1.5 S0912 3 swim outdoor 1 DR1810 4 bball both 3 DR1811 5 vball indoor 1.5 DX0010 6 vball indoor 1 DX2000 7 tennis outdoor 2 Now can i do a count by ID (first 4 letters) Mean by S091 will give 3, DR18 will give 2 and DX00 will give 1 and DX20 will give 1. Can this be dong? "Dave Peterson" wrote: =counta(b2:b9999) will give you the total number of cells in B2:B9999 that have anything in it. =sum(d2:d9999) will sum the total hours in D2:D9999 =countif(b2:b9999,"swim") will count the number of cells that are equal to "swim" =sumif(b2:b9999,"swim",d2:d9999) will sum the values in D2:D9999 where the value in column B (same row) is equal to "swim" You may want to learn more about Data|Pivottables (xl2003 menu system). It could make this kind of summary report lots easier. kyoshirou wrote: Hi All, I would like to do a auto tabulate formula for this problem, rather than i go calucate from time to time. Let's say if i have a table like this: No Game Type Hours --- ------ ----- ------ 1 swim outdoor 2 2 jog indoor 1.5 3 swim outdoor 1 4 bball both 3 5 vball indoor 1.5 6 vball indoor 1 7 tennis outdoor 2 With the above table, the excel will auto give me a drop down (i dont know what does this called), it will help me to group under 1) jog, swim bball, vball & tennis. 2) indoor,outdoor & both. 3) 0.25,1,3 & 1.5 __________________________________________________ ______________ Now i trying to create a calculation by having this: Total | Swim | Jog | BBall | vBall | Tennis count 7 2 1 1 2 1 Hours 12 3 1.5 3 2.5 2 If i using formula inside the fx=count for Total, it will be simple. But how do i calculate for other like Swim, Jog, etc THanks~ -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|