Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplifying statistical spreadsheets | Excel Discussion (Misc queries) | |||
Need statistical help | Excel Worksheet Functions | |||
Can Excel Spreadsheets be saved as Microsoft Works spreadsheets? | Excel Discussion (Misc queries) | |||
Statistical add-ins | Excel Discussion (Misc queries) | |||
Statistical Function | Excel Worksheet Functions |