# Formula for irregular row divisions

1. ## 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  Register To Reply

2. ## 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   Register To Reply

3. ## Re: Formula for irregular row divisions

My apologies, see the day delineations now Try this in C2:
Formula:  `Please Login or Register  to view this content.`

Drag/Extend down

Hope this helps   Register To Reply

4. ## 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...  Register To Reply

5. ## 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   Register To Reply

6. ## 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!  Register To Reply

7. ## 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:  `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   Register To Reply

8. ## 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.  Register To Reply

9. ## Re: Formula for irregular row divisions

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

10. ## 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 .  Register To Reply

11. ## 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   Register To Reply

12. ## 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   Register To Reply

13. ## 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   Register To Reply

14. ## Re: Formula for irregular row divisions

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

15. ## 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   Register To Reply

16. ## 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.  Register To Reply

17. ## Re: Formula for irregular row divisions

I went a slightly different way
In D1 put date (01/01/1982), then this in D2:
Formula:  `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)  Register To Reply

18. ## 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  Register To Reply

19. ## 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"  Register To Reply

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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