Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable # of data rows (Excel 97)

    My wife and I developed a basic spreadsheet with which my supervisor could keep track of monthly statistical information for a monthly report he needs to generate. The spreadsheet adds and averages certain columns of information. What I'd like for the sheet to do is to provide just as many rows as may be needed on any given month. Some months he may have 20 or so rows of data and yet on others he may have 30 or more.

    Is there a way to have such a spreadsheet that will add rows as needed and just sum and average those columns that are used? Right now, to ensure my boss has enough rows/columns to handle the amount of data he might have in any given month, I am providing about 25 rows on the sheet. This past month the amount of stats required more capacity so he just used two of the sheets. I'd like for him to be able to just keep adding info as needed and, at the end of the month, the sheet will have his running totals & averages at the bottom without a bunch of empty rows in between the last entry and the final totals/averages.

    Just FYI, for those who may be curious, this sheet is to compute motor vehicle collision stats (drivers' ages, damage estimates, etc.) for my supervisor to present to the Chief of Police monthly. A copy of the spreadsheet is attached

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable # of data rows (Excel 97)

    Probably the handiest way would be to include a blank row just above your summary data, and each time you need to a new record select the blank row, and insert a new row just above it. So if your blank row is 21 and the summary formual is =AVERAGE(E3:E21), then when the new record is added the formula should automatically adjust to =AVERAGE(E3:E22), thus ensuring the inclusion of the new record.

    Andrew C

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Variable # of data rows (Excel 97)

    Steve,
    I was in Bartlesville, Ok once a long time ago.

    Take a look at the example sheet in the attachment.
    It has a slightly different approach that some will like, others won't.

    Regards,
    Jim Cone
    San Francisco, CA

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable # of data rows (Excel 97)

    If you add the summary at the top, you might want to include it in the "Freeze panes" so you can always see it.

    Generally, I prefer, to put the summary line(s) above the header row(s) and then the data so there is still a clear separation. But I like to always see the summary row and will even include it in the "rows to repeat at top" when printing.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable # of data rows (Excel 97)

    Jim,

    Thanks for taking the time to respond to my post. Sorry for the tardy response but I've been a bit busy.

    I will show your idea to my boss to see if he would like to incorporate it in the sheet. I'm not very literate in function statements but I looked it up in Excel's on-line Help and got some basic info about it. Pretty interesting stuff, I might add.

    Thanks again,

    -Steve-

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable # of data rows (Excel 97)

    Steve,

    Having the totals & averages at the top of the sheet struck me as interesting as well as different. As I told Jim in my response to his reply, I'm going to make the suggestion to my boss to see if he wants to incorporate any of Jim's (and your) idea into his worksheet.

    Thanks for responding.

    -Steve-

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable # of data rows (Excel 97)

    Andrew C.,

    Thank you for taking the time to reply to my post.

    As coincidence would have it, my wife had made the same suggestion you did the evening before I read your response. I tried it out and, sure enough, the formula adjusts to accommodate the added row(s).

    Thank you for your response.

    -Steve-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •