MS Excel Help - AR15.COM

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

Posted: 1/9/2006 11:19:24 PM EDT

[#1]

read up on "pivot tables"

Posted: 1/9/2006 11:40:28 PM EDT

[#2]

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

)

Posted: 1/9/2006 11:45:26 PM EDT

[#3]

Quoted:
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

Posted: 1/9/2006 11:55:36 PM EDT

[#4]

Quoted:
Quoted:
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).

Posted: 1/9/2006 11:58:23 PM EDT

[#5]

ETA: My bad, waiting on skippy to clarify

Posted: 1/10/2006 12:06:42 AM EDT

[#6]

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.

Posted: 1/10/2006 12:06:43 AM EDT

[#7]

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.

Posted: 1/10/2006 12:07:25 AM EDT

[#8]

Quoted:
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.

Posted: 1/10/2006 12:13:40 AM EDT

[#9]

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.)

Posted: 1/10/2006 12:19:15 AM EDT

[#10]

Quoted:
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)

Posted: 1/10/2006 4:45:23 PM EDT

[#11]

BTW, there's an average function so you don't have to do the averaging manually>>> "=AVERAGE(C$2:C*)"

Posted: 1/10/2006 5:06:52 PM EDT

[#12]

Quoted:
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.

Posted: 1/10/2006 5:10:44 PM EDT

[#13]

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.

Posted: 1/10/2006 5:18:09 PM EDT

[#14]

Quoted:
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.

Posted: 1/10/2006 6:28:51 PM EDT

[#15]

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.

[ARCHIVED THREAD] - MS Excel Help

General » General Discussion

Warning

Confirm Action

About AR15.COM

Stay Connected

Newsletter

Contact Us