Help with statistical spreadsheets
 

Hello,
Am I able to attach a spreadsheet to my post so that someone can help me
with it please? I'm trying to streamline the way my dept records stats and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc, but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP

Help with statistical spreadsheets
 

You could upload a sample of your file,
then post the link to it here

Use either of these free filehosts:
http://www.freefilehosting.net/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote in message
...
Hello,
Am I able to attach a spreadsheet to my post so that someone can help me
with it please? I'm trying to streamline the way my dept records stats and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc, but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP

Help with statistical spreadsheets
 

Thanks Max,
I have uploaded the file, please access on the link below.

http://www.freefilehosting.net/download/3a323

Look forward to hearing back from you.
--
RMP


"Max" wrote:

You could upload a sample of your file,
then post the link to it here

Use either of these free filehosts:
http://www.freefilehosting.net/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote in message
...
Hello,
Am I able to attach a spreadsheet to my post so that someone can help me
with it please? I'm trying to streamline the way my dept records stats and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc, but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP

Help with statistical spreadsheets
 

You seem to have things neatly arranged & under control as far as I can see
?
(your layout is not suitable for pivot, but this is not relevant here)

Eg your typical point cell formula in Weekly Summary's B3:
=SUM('Week 1'!B3,'Week 1'!F3,'Week 1'!J3,'Week 1'!N3,'Week 1'!R3)
is easily propagatable across/down for the week

Similarly with the next one in F3:
=SUM('Week 2'!B3,'Week 2'!F3,'Week 2'!J3,'Week 2'!N3,'Week 2'!R3)

And the same thing goes for the formulas in Monthly Summary

Maybe just a small point on using Edit Replace for replicating formulas in
this kind of scenario:
To "replicate" B3's formula for F3. You could say, remove the equal sign
temporarily in B3's formula, then copy the formula n paste into F3. Then
just use EditReplace to replace (in F3): Week 1 with: Week 2, then
re-instate both equal signs in B3 & F3.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote in message
...
Thanks Max,
I have uploaded the file, please access on the link below.

http://www.freefilehosting.net/download/3a323

Look forward to hearing back from you.
--
RMP

Help with statistical spreadsheets
 

Thanks again Max that is very helpful.

I have managed to replicate the formula in from B3 to F3, J3 and N3.

Is there any easy way of copying the formula from B3 to B4. I have tried to
drag the formula down but it's copying the result from B3 even though the
formula refers to the sum of B4, F4, J4 and N4 on the Week 1 sheet.

I hope to be able to enter the formulas in the top row and drag them all the
way down.

Thanks again.
--
RMP


"Max" wrote:

You seem to have things neatly arranged & under control as far as I can see
?
(your layout is not suitable for pivot, but this is not relevant here)

Eg your typical point cell formula in Weekly Summary's B3:
=SUM('Week 1'!B3,'Week 1'!F3,'Week 1'!J3,'Week 1'!N3,'Week 1'!R3)
is easily propagatable across/down for the week

Similarly with the next one in F3:
=SUM('Week 2'!B3,'Week 2'!F3,'Week 2'!J3,'Week 2'!N3,'Week 2'!R3)

And the same thing goes for the formulas in Monthly Summary

Maybe just a small point on using Edit Replace for replicating formulas in
this kind of scenario:
To "replicate" B3's formula for F3. You could say, remove the equal sign
temporarily in B3's formula, then copy the formula n paste into F3. Then
just use EditReplace to replace (in F3): Week 1 with: Week 2, then
re-instate both equal signs in B3 & F3.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote in message
...
Thanks Max,
I have uploaded the file, please access on the link below.

http://www.freefilehosting.net/download/3a323

Look forward to hearing back from you.
--
RMP

Help with statistical spreadsheets
 

If your formula is referring to column 4 but you are seeing a column 3
result, then I suspect that in Tools/ Options/ Calculation you may have
selected Manual where you ought to have Automatic.
--
David Biddulph

"RMP" wrote in message
...
Thanks again Max that is very helpful.

I have managed to replicate the formula in from B3 to F3, J3 and N3.

Is there any easy way of copying the formula from B3 to B4. I have tried
to
drag the formula down but it's copying the result from B3 even though the
formula refers to the sum of B4, F4, J4 and N4 on the Week 1 sheet.

I hope to be able to enter the formulas in the top row and drag them all
the
way down.

Thanks again.
--
RMP

"Max" wrote:

You seem to have things neatly arranged & under control as far as I can
see
?
(your layout is not suitable for pivot, but this is not relevant here)

Eg your typical point cell formula in Weekly Summary's B3:
=SUM('Week 1'!B3,'Week 1'!F3,'Week 1'!J3,'Week 1'!N3,'Week 1'!R3)
is easily propagatable across/down for the week

Similarly with the next one in F3:
=SUM('Week 2'!B3,'Week 2'!F3,'Week 2'!J3,'Week 2'!N3,'Week 2'!R3)

And the same thing goes for the formulas in Monthly Summary

Maybe just a small point on using Edit Replace for replicating formulas
in
this kind of scenario:
To "replicate" B3's formula for F3. You could say, remove the equal sign
temporarily in B3's formula, then copy the formula n paste into F3. Then
just use EditReplace to replace (in F3): Week 1 with: Week 2, then
re-instate both equal signs in B3 & F3.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote in message
...
Thanks Max,
I have uploaded the file, please access on the link below.

http://www.freefilehosting.net/download/3a323

Look forward to hearing back from you.
--
RMP

Help with statistical spreadsheets
 

Just Curious do you manual type the figures in every day?
I can try to stream line it for yuo if your interested



"RMP" wrote:

Hello,
Am I able to attach a spreadsheet to my post so that someone can help me
with it please? I'm trying to streamline the way my dept records stats and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc, but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP

Help with statistical spreadsheets
 

As David says, think its just your book's calc mode "inadvertently" set to
manual. Reset it to auto mode as per David's response (ToolsOptionsCalc
tab)

Note that in manual calc mode, you can always press F9 to re-calculate
whenever required.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote:
Thanks again Max that is very helpful.

I have managed to replicate the formula in from B3 to F3, J3 and N3.

Is there any easy way of copying the formula from B3 to B4. I have tried to
drag the formula down but it's copying the result from B3 even though the
formula refers to the sum of B4, F4, J4 and N4 on the Week 1 sheet.

I hope to be able to enter the formulas in the top row and drag them all the
way down.

Thanks again.
--
RMP

Help with statistical spreadsheets
 

Hi

If you did want to use Pivot Tables, I have uploaded your file set out for
use with PT

http://www.freefilehosting.net/download/3a3c9

I have read across just your Week1 data to a format suitable for PT analysis
on a sheet called Data, and sheet Report is a Pivot Table report based on
just that week of data.
I assumed Monday of week1 was 31 Dec 2007 for this purpose.
There is a sheet called Setup, with Validation Lists for Data entry.

On Sheet Data, in the next available row, there are dropdowns to select the
Category, Type and Name.
Column F inserts the Day of the week if this is required, and column G
calculates a week number (forcing Monday 31 Dec 2007 to be in Week 1)

You could hide columns F and G as far as Data entry is concerned, so the in
putter would only have to enter Name, Date, Category, Type and Number
available to them.

On the report you could just select an individual week, or a Month.
You could select just any given day if required and see the results.
In the longer term, this would lead to less work in maintenance as you add
more and more weeks throughout the year..

If you found it easier to do day entry with your existing layout, then you
could just input one day's worth of data, (i.e. just columns A to E ) with
Date in place of Day.
Then use a short macro to write the data out to your data Sheet.
I have included the code to do this in a macro called ConvertData.

Note this assumes that the Source sheet for data entry is still called Week
1 (change in code if necessary) and that the destination sheet is called
Data

--

Regards
Roger Govier

"RMP" wrote in message
...
Thanks Max,
I have uploaded the file, please access on the link below.

http://www.freefilehosting.net/download/3a323

Look forward to hearing back from you.
--
RMP


"Max" wrote:

You could upload a sample of your file,
then post the link to it here

Use either of these free filehosts:
http://www.freefilehosting.net/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RMP" wrote in message
...
Hello,
Am I able to attach a spreadsheet to my post so that someone can help
me
with it please? I'm trying to streamline the way my dept records stats
and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc,
but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP

Help with statistical spreadsheets
 

Hi there,
Yes the figures are input daily. I'd be more than happy for a streamlined
process.

You guys are great, thanks to David, Roger and Max!!!

Thanks again
--
RMP


"CmK" wrote:

Just Curious do you manual type the figures in every day?
I can try to stream line it for yuo if your interested



"RMP" wrote:

Hello,
Am I able to attach a spreadsheet to my post so that someone can help me
with it please? I'm trying to streamline the way my dept records stats and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc, but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP

Help with statistical spreadsheets
 

I just realised i have to ask you a million question
cant do it over the message board


"RMP" wrote:

Hi there,
Yes the figures are input daily. I'd be more than happy for a streamlined
process.

You guys are great, thanks to David, Roger and Max!!!

Thanks again
--
RMP


"CmK" wrote:

Just Curious do you manual type the figures in every day?
I can try to stream line it for yuo if your interested



"RMP" wrote:

Hello,
Am I able to attach a spreadsheet to my post so that someone can help me
with it please? I'm trying to streamline the way my dept records stats and
the spreadsheet is becoming more complex and difficult to manage as I
progress.

I'm sure there is an easier way to set it up with pivot tables etc, but, I
lack the knowledge and experience to use pivot tables.

Any help would be invaluable and much appreciated.

Thanks
--
RMP