Help with statistical spreadsheets

  • Thread starter Thread starter RMP
  • Start date Start date
R

RMP

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
 
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 Edit>Replace to replace (in F3): Week 1 with: Week 2, then
re-instate both equal signs in B3 & F3.
 
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.
 
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.
 
Just Curious do you manual type the figures in every day?
I can try to stream line it for yuo if your interested
 
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 (Tools>Options>Calc
tab)

Note that in manual calc mode, you can always press F9 to re-calculate
whenever required.
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting statistics 5
Work laptop issues 4
pivot table counting 1
Pivot table question 3
Data the same but different 8
Soccer stat spreadsheet help 1
Pivot Cache - Massive Spreadsheet 3
Simplifying statistical spreadsheets 5

Back
Top