Warning

 

Close

Confirm Action

Are you sure you wish to do this?

Confirm Cancel
Member Login
Posted: 1/9/2006 10:38:16 PM EDT
Ok I'm not an excel guru. i can do the basic stuff. This has me stuck.

I have a series of data (PBT results) that correspond to a date taken. I want to track the average over the dates. i.e. 2,4, 6, 4 would have averages 2,3,4,4 etc. Basically I want to be able to look at a date and see the average of all the data at that date then on the next date when a new number is added I can see the new average and how it was affect by the new number.

How do I do this. I think I can do it in a line graph and got that to go but I want it in a data series.

Thanks!

Adam
Link Posted: 1/9/2006 11:19:24 PM EDT
read up on "pivot tables"
Link Posted: 1/9/2006 11:40:28 PM EDT
I think that would require some formulas...

I think you'd only have to write it once or twice then you could drag it to apply... I guess I'd have to do it myself to be 100% sure (I'm a certified MS Excel 2003 specialist , but havent done any new spreadsheets in a while )
Link Posted: 1/9/2006 11:45:26 PM EDT

Originally Posted By sWs2:
I think that would require some formulas...

I think you'd only have to write it once or twice then you could drag it to apply... I guess I'd have to do it myself to be 100% sure (I'm a certified MS Excel 2003 specialist , but havent done any new spreadsheets in a while )



It would require that you input a formula in the cells. Rather than dragging to apply to the selected area, you could just leave a lot of room in between the data and the formula. The formula would then cover your first input to your formula so even if you added new data in a cell the formula would still work.

I might not be the best at explaining it...im kinda a hands on guy.....but this is how you do it....

-College Student
Link Posted: 1/9/2006 11:55:36 PM EDT

Originally Posted By BlackLex:

Originally Posted By sWs2:
I think that would require some formulas...

I think you'd only have to write it once or twice then you could drag it to apply... I guess I'd have to do it myself to be 100% sure (I'm a certified MS Excel 2003 specialist , but havent done any new spreadsheets in a while )



It would require that you input a formula in the cells. Rather than dragging to apply to the selected area, you could just leave a lot of room in between the data and the formula. The formula would then cover your first input to your formula so even if you added new data in a cell the formula would still work.

I might not be the best at explaining it...im kinda a hands on guy.....but this is how you do it....

-College Student



Heh, I think we're thinking of something similer, but Its hard to explain (Excel is pretty much hands on stuff anyhow).
I'm thinking of something like this, though I'm not sure if that is what he wants. An alternate is to only have one cell somewhere displaying the final average (but not show a daily running average).
Link Posted: 1/9/2006 11:58:23 PM EDT
[Last Edit: 1/10/2006 12:00:42 AM EDT by DukeSnookems]
ETA: My bad, waiting on skippy to clarify
Link Posted: 1/10/2006 12:06:42 AM EDT
I have the final average. The eample sWs2 posted is close. Maybe my math is off but on the third day 2+6+4=12 and 12/3=4 not 4.5. 4 would be the average for the 3 days. I want to look at the table at any point and see the average up to that date.

Thanks guys and I hope this helps.
Link Posted: 1/10/2006 12:06:43 AM EDT
In all honesty I've never heard of a pivot table till just now. Evidently its not need to know info to get certification. Though upon looking it up I see how very useful it could be in certain situations.
Link Posted: 1/10/2006 12:07:25 AM EDT
[Last Edit: 1/10/2006 12:18:10 AM EDT by sWs2]

Originally Posted By skippyputz:
I have the final average. The eample sWs2 posted is close. Maybe my math is off but on the third day 2+6+4=12 and 12/3=4 not 4.5. 4 would be the average for the 3 days. I want to look at the table at any point and see the average up to that date.

Thanks guys and I hope this helps.


Ah.. I didnt realize you were doing it that way...I was adding the new score to the previous average, then averaging that.
ETA:It looks like to do what you want and have a running daily average, instead of one final one, you would have to manually enter in a new formula for each row. Howeve, if you wan one final average thats pretty easy.

AHA I think I got it now.
Link Posted: 1/10/2006 12:13:40 AM EDT
I'm a self taught excel person. You guys are great for helping. I don't think I coud do this for my job. (I went to school to be a cop. They didn't cover this there. I get tasked with this kind of stuff because my boss wouldn't have the first clue. I could always pawn it off but I hate doing that to the Sgts.)
Link Posted: 1/10/2006 12:19:15 AM EDT
[Last Edit: 1/10/2006 12:26:53 AM EDT by sWs2]

Originally Posted By skippyputz:
I'm a self taught excel person. You guys are great for helping. I don't think I coud do this for my job. (I went to school to be a cop. They didn't cover this there. I get tasked with this kind of stuff because my boss wouldn't have the first clue. I could always pawn it off but I hate doing that to the Sgts.)


I dont do this for a job, it was required for pre-business majors (such as myself).

Alternativly for the formula shown above you could use =AVERAGE($B$2:B6) and delete the first column entirely. (I think slow at 2 am)
Link Posted: 1/10/2006 4:45:23 PM EDT
[Last Edit: 1/10/2006 4:45:47 PM EDT by DukeSnookems]
BTW, there's an average function so you don't have to do the averaging manually>>> "=AVERAGE(C$2:C*)"
Link Posted: 1/10/2006 5:06:52 PM EDT

Originally Posted By sWs2:
I think that would require some formulas...

I think you'd only have to write it once or twice then you could drag it to apply... I guess I'd have to do it myself to be 100% sure (I'm a certified MS Excel 2003 specialist , but havent done any new spreadsheets in a while )



Sounds correct but I did get a B in Excel. Formulas click and drag.
Link Posted: 1/10/2006 5:10:44 PM EDT
use comand average(A1:A100)
A1being your first element you want to average, A100 being the last. I know many tricks on excell so IM if you have any questions.
Link Posted: 1/10/2006 5:18:09 PM EDT

Originally Posted By deathdealer1488:
use comand average(A1:A100)
A1being your first element you want to average, A100 being the last. I know many tricks on excell so IM if you have any questions.



No, you need to anchor the A1 as A$1 or else you'll get a rolling average when you drag your formula.
Link Posted: 1/10/2006 6:28:51 PM EDT
Here's an easy way to get what you want with minimal data entry. In this example, the only thing that you have to fill in is the new number. Everything else can be dragged down.

Top Top