+ Reply to Thread
Results 1 to 19 of 19

Formula for irregular row divisions

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Formula for irregular row divisions

    Greetings,
    I have computer model output files that have >100,000 rows. Column A is the model time (days). Column B is a rate (meters/day). Each day is broken up into pieces, but not everyday is broken into the same number of pieces, i.e. day 1 consists of 22 rows while day 2 consists of 5 rows. What I need is the Average rate (meters/day) for each day, i.e. averaging the 22 rows for day 1 in Column B and averaging the 5 rows for day 2 in Column B, etc... I'm trying to write a formula that I can cut and paste because I have to do this for over 130 different model outputs. I attached an example of the data. Any help is much appreciated.
    Thank You,
    Zach
    Attached Files Attached Files

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    Your sample is unclear, what values correspond to which day?, what is expected output (and where) for at least a couple of samples, and why is it the expected output?
    all I see is a bunch of numbers with no explanation of what you expect or where its supposed to appear
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    My apologies, see the day delineations now

    Try this in C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag/Extend down

    Hope this helps

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    dredwolf,
    Sorry if my explanation was unclear. It's hard to put into words exactly what I need help with.
    In my attached example spreadsheet, Column A is the model run time and Column B is the rate at that time in the model. Example, at time equals 0.001 days, the rate is -0.00056305 m/day (Row 2 for Columns A and B, respectively) (*NOTE: the negative rate value are because it's in the downward direction).
    In the spreadsheet, day 1 (time equals 0.001–0.8045) is 21 rows. Day 2 (time equals 1–1.8045) is 22 rows. Day 5 (time equals 4–4.625) is 3 rows.
    My goal is to get the average Rate (m/day) for each year (Time equal to 0–364 days, 365–730 days, 731–1095 days, etc...). The full spreadsheet is 30 years (10,956 days), and I have 132 spreadsheets
    Therefore, I would love to have a formula so I can copy and paste into the other 131 spreadsheets after I complete the first spreadsheet.
    What I don't know is how to write a formula that makes Excel average only the values in Column B that correspond to time equal to 0–364, 365–730, 731–1095, etc...

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    Try my last (post #3) to see if we are getting close, then we will see if we can work the rest out

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    dredwolf,
    Thank you so much! Yeah made a mistake in first post, I need yearly averages, not daily, but your formula looks to work for daily. I have no idea what it means , but the values are correct! Do you what I would need to change for it to compute yearly averages?
    Thanks Again!

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    okay, for yearly, we have a problem, calculating leap years, but the basic solution (without leap years) would be:
    (REVISED)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    copied down

    the formula to take into account leap years may be far different though, not even sure I can figure it out in a short time, but would definitely need the start year to even have a hope of being accurate

    EDIT-
    see (REVISED) for proper formula
    Last edited by dredwolf; 04-04-2013 at 10:11 PM.

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    dredwolf,
    Wow, thank you! This is saving my life . If you're up to it, the 3rd year is the first leap year.

  9. #9
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    The start year is 1982. 1984 is the first leap year in the series.
    Thanks

  10. #10
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    Much thanks to dredwolf for your help, I really appreciate it. If anyone knows how to edit dredwolfs above formula for annual averages to account for leap years, I would be forever in your debt .

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    I am working on it, but as i suspected, the leap year is causing me some problems, I am getting close, but the year AFTER the leap year is being calc'ed as leap year, so needs refinement...I HAVE NOT given up trying though

  12. #12
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    Thank you very much!!! Leap years really mess up efficiency when using Excel. Good luck and many thanks for even trying

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    You are welcome
    Just want you to know, probably another 18 hrs plus before I have an acceptable to formula, I need to test it !
    (My RL work life precludes me spending all night trying, although, it IS tempting )

    Thanks for the originating year, it DOES make a difference

  14. #14
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    Glad you're enjoying the challenge It would take me 18 YEARS to complete anything like this.

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    Well, in all likely hood, I'll post my solution, then someone will come up with a better one, but that's okay, , the goal is to give you the best solution we can come up with

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Formula for irregular row divisions

    See the attached file with formula in F column , Which takes care of Leap year also. I hope this is what you want.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 04-06-2013 at 07:04 AM.

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    I went a slightly different way
    In D1 put date (01/01/1982), then this in D2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag/extend down to bottom of list

    I Had to delete the formula from the rest of the column, and get rid of formatting to make the file uploadable, but the formula is in D2, and the Daily average formula is still in C2, in case you want it as well

    Hope this helps

    EDIT-
    the filters allow you to just see the information you want (ie- uncheck the 'blanks' option box for the yearly averages only)
    Attached Files Attached Files
    Last edited by dredwolf; 04-07-2013 at 01:00 PM.

  18. #18
    Registered User
    Join Date
    04-04-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula for irregular row divisions

    dredwolf and kvsrinivasamurthy,
    Thank you both very much!!!!! This is a HUGH help!!! Saving me hours of time and millions of headaches
    -Zach

  19. #19
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for irregular row divisions

    You are welcome

    Please remember to mark the thread as solved if you are satisfied with your solution :
    To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1