• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to define a dynamic range to find daily statistics

SimonK

SimonK

New Member
I have data that was collected hourly from a data logger and I want to condense the data to daily statistics (max, min and mean). Please see the attached file. I need to define the dynamic range for each date to calculate respective stat for each day. I've managed to identify the cells at the start and end of each day but I can't find a way to put this into a formula. There are probably other ways to work this out so I'm open to any help with this. Thanks in advance for help. :)
 

Attachments

  • Dynamic Range problem.xlsx
    552.2 KB · Views: 6
Nebu said:
Hi:

Is this what you are looking for. I have used a pivot to summarise the data.

Thanks
Click to expand...

Thank you Nebu, Yes I thought about using a pivot table but I have lots of these to do so copying and pasting formula is easier than generating multiple pivots.
 
NARAYANK991 said:
Hi ,

See the attached file.

Narayan
Click to expand...
Hi Narayan,
I'm a bit baffled as to why your formulas for max and min are not working when I apply them to my larger data file. Attached is an example of what I mean. I'm curious as to what {} mean as I haven't used these before in Excel.
 

Attachments

  • Dynamic Range2.xlsx
    809.9 KB · Views: 3
SimonK said:
Hi Narayan,
I'm a bit baffled as to why your formulas for max and min are not working when I apply them to my larger data file. Attached is an example of what I mean. I'm curious as to what {} mean as I haven't used these before in Excel.
Click to expand...
Hi ,

The curly braces are automatically inserted by Excel , when you enter a formula using the combination of the 3 keys CTRL SHIFT ENTER ; this is done when you wish to enter an array formula.

Press the CTRL key , and keeping it pressed , press the key SHIFT ; keeping these two keys pressed , press the ENTER key. This will enter the formula as an array formula , and Excel will surround the formula with the curly braces.

See the following links for more details :

http://www.cpearson.com/excel/arrayformulas.aspx


Narayan
 
You must log in or register to reply here.
Back
Top