Excel Magic Trick 1471: Array Formula: Add Daily Rainfall 5 AM to 5 AM Next Day: (Most Automatic)

Learn about how to
In this video learn how to use Array Formulas to automate the reporting process for Rain Totals where the staring time for a day period changes.
Topics:
1. (00:01) Introduction and description of reporting requirements
2. (02:08) Date and Time Number Formatting and Time Formula for Time Equivalent for Date Start Time.
3. (03:32) Count Unique Date Array Formula. See the functions FREQUENCY, SUM and INT. See two versions of this formula. One version converts positive numbers with the Logical Test Greater than Zero and a Double Negative. The other uses the SIGN Function.
4. (13:34) Array Formula to Extract a List of Unique Dates that assumes that we may not have a complete list of sequential dates, and there is a variable Start Time. See the functions AGGREGATE, ROW, ROWS, IF, SIGN, FREQUENCY, SUM and INT.
5. (26:02) Formula to add rain totals using SUMIFS & IF Functions and criteria based on the start date-time.
6. (28:39) Array Formula to Extract a List of Unique Dates that assumes we HAVE a complete list of sequential dates. See the functions, AGGREGATE, IF, ROWS and INT.
7. (34:09) Re-organize Order of Logical Tests in IF Function to create a more efficient formula.
8. (36:03) Add new data and test formulas and reporting system.
9. (36:50) Summary
Related Videos:
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day

[LeilaGharani]

Thank you for this array fun-packed video! Both solutions are great! I like how you used the Frequency and SIGN functions :)

[johnborg]

I cannot put another Thumbs up....sorry. Totally amazing!!!

[ExceliAdam]

Great Array formula.It's the first time I've seen the SIGN function used this way, thanks.

[jerrydellasala]

The techniques used in this video are STILL incredibly useful, but doing this with Excel 365 new Array Calc Engine functions (which I've learned with your more recent videos) makes this SO much easier. Shared here for other Excel junkies with Column Header and the formula:
Count Days
Equivalent]])))
Start Date-Time
Equivalent]))+Hour[Time Equivalent]
Rain Total for >=Start & <End
=SUMIFS(RainFall[Raintotal (Unit?)], RainFall[TIMESTAMP], ">="&I5#, RainFall[TIMESTAMP], "<"&I5#+1)

The new formulas not only negate the need for an alternate formula for the Start Date-Time column, but also only need to be entered in the top column. No error checking needed for Error/No result.
Note the use of the full column notation # in the Rain total formula to "pull" it down the results of the Start Date-Time column.
Not only are the formulas so much easier to use, but what they're doing is clearer. (BTW, the Start has all the formulas and data as well as an (a) worksheet)

[Victor-ollo]

Array formulas are simply awesome !!! Thanks Mike.

[mohamedchakroun]

Waoooo array formulas are crazy formula without you methodic explanation they wont sink in :-)

[togetherweexcel]

As usual... Outstanding.
There's so much to learn from you sir!

[pmsocho]

That is a crazy array solution! :)
Thanks for the awesome explanation!

[ismailismaili]

thank you so much it was so difficult for me to know how to calculate it but at lest i know some formulas how they work and i know that the array formulas are Index, Aggregate, Lookup.. thank you so much for the video

[SyedMuzammilMahasanShahi]

EXCELlent solution by amazing MIKE. Thanks man

[lichaccakaven]

Merry Christmas Mike!! Love you and your videos!!!

[Al-Ahdal]

Mike just ordered Ctrl+Shift+Enter Book + DVD, very excited. Kindly advise, are the contents more in depth then provided in the videos on the said topic? Very excited to get it next month through Amazon.

[MySpreadsheetLab]

I'm looking forward to this! I love your ARRAY solutions! Merry Christmas Mike!!

[MaartnII]

Hi,
I'm making a custom japanese puzzle/nonogram in excel and i need to count cells that are equal and next to eachother. So for example, i have a row of colored cells (here indicated by letters): AAABBBCCBBBAABACCDA. These letters represent the background color of those cells in a row. Now i need a fast way to count the cells that are equal and next to eachother. The solution for this set of letters would be: A3, B3, C2, B3, A2, B1, A1, C2, D1, A1. I don't need to know the total amount of a specific kind of cells so countif is no option. So I need a way to do this efficiently because an image of 400 rows by 250 columns is quite tedious to do on your own.

[gerardrowell]

Hello. I Have a question of how to do an excel worksheet. I have a names of employees. Each employee takes between 3 weeks of vacation. I have the worksheet completed that tell me what weeks each employee has, however, how can i make a new worksheet (using the completed worksheet info), to show what employee is on vacation by week.
(example: Bill is on vacation (weekending date) 1/6/18, 7/7/18, 7/28/18
Pam 1/6/18, 3/17/18, 4/28/18
Chris 3/17/18, 7/7/18, 9/1/18

Now new sheet will show Under date 1/6/18, Bill and Pam, Under date 3/17/18, Pam and Chris. Under date 7/7/18, Bill and Chris . . .etc

[datadev]

Great tip but now my brain hurts. Thanks