Extract Peak Value with Date & Timestamp

We are needing to find the peak or Max of the Value column and display along with the date and time of that Max value Then perform the same task for other meters which could be in the same file. We would then totalize those values providing one usable value.

Seems like a combination of MAXIFS() and a lookup function should make short work of this task. =MAXIFS(value column,meter# column,meter#) will find the max for a given meter#
=MATCH(reference to MAXIFS,value column,0) will give the row # corresponding to a given maximum value -- assuming there are no duplicates in the value column.
=INDEX(date column,reference to MATCH() function) will return the corresponding date.
=INDEX(time column,reference to MATCH() function) will return the corresponding time.

The only complication I foresee is if there could be multiple copies of the same value in the value column. If the maxima will always be unique, this should work.

Then for a totally separate calcualtion, we would need to be able to find the value for each device based on a date and time that we enter?

That seems like a simple lookup function. The main difficulty is that your date and time values seem to be separate columns in the current sheet. I would combine them into one date+time column, then use that column for the lookup.

In source data, assuming date and time are real date/time serial numbers and not text strings, add column =date+time
enter your date+time lookup value in a single cell, then use a lookup function to find it =INDEX(value column,MATCH(entered data/time,date+time helper column,1)) I would try to sort the list by date/time so I could use the much faster approximate match option for this. You may also need to account for floating point errors in these calculations.

Originally Posted by MrShorty

The only complication I foresee is if there could be multiple copies of the same value in the value column. If the maxima will always be unique, this should work.

There is a possibility the same max value is duplicated in the value column with consistent electric loads. The date/time really doesn't matter I suppose for the top part of what we need, mostly just the value. We primarily just need the peak.

The second part would require the date/time as we essentially would query a date/time and want the value returned for both meters and then totalized. I hope all that makes sense.

The first part is their non-coincidental peak meaning their total energy demand for the entire month. The second part is their peak demand at the same exact date/time as the entire state of Texas which is their coincidental peak.

The date/time really doesn't matter I suppose for the top part of what we need, mostly just the value. We primarily just need the peak.

A MAXIFS() or a pivot table should easily be able to tell you the maximum value for each meter. Either option is going to work better if the data are stored in a simple, "flat" file where the data for meter 5001 is immediately beneath the 5000 data. In the long run, make sure you are maintaining this database in a good database format.

Assuming your data are arranged in a good database, I would probably use a pivot table for this. Select the database -> Insert pivot table -> "meter" as row label and "max of KW" as the values.

I observe that your "dates" are not real Excel dates. It may depend on what exactly you intend to do with these data in the long term, but I could see value in converting your dates (or changing the date format on the meter reading program that generates the data) to something Excel can use as a date.

The second part would require the date/time as we essentially would query a date/time and want the value returned for both meters and then totalized. I hope all that makes sense.

With the data in a good database, this might be easiest with a filter rather than a formula. https://www.wikihow.com/Use-AutoFilter-in-MS-Excel With an autofilter set up, you can click on the drop downs, enter the meter #, the date, and the time and get the value. A lookup function will want the meter#, date, and time combined somehow into a unique lookup value that you can then use for a search, or you end up using the DGET() function, which seems to be in disfavor.

Part of me would want to understand exactly how you are intending to use this spreadsheet in the long term, but it seems that there will be value in making sure to treat this is a database and build it accordingly.

I don't do any real database work, either, so I'm probably not the best to get advice from.

It appears that you are getting some of the data from another database and then getting some data directly from individual meters. Even as a non-database user, I could see value in someone being tasked with bringing all of the data from these different sources into a single, well maintained database. I guess it depends on what you intend to do with the data in the future, and how valuable this data is. In the information age, data usually has value, but it will have much less value if it is scattered around or difficult to query.

As far as the immediate question, it appears that you are getting a bunch of data from different sources, and you want to then summarize and analyze that data. It often seems that the easiest way to do that is to combine all of the data into a good database in Excel (assuming you decide to use Excel for the data storage part), then use Excel's built in database tools to query and summarize that data. I have no experience with it, but Power Query/Get and Transform might be the easiest way in Excel to bring the different data sources together into a single data source. https://support.office.com/en-us/art...e-5bfb1a6c536a Power Query may also have some useful tools for analyzing the data beyond what Excel's built in pivot table and filter/query tools offer.

It all probably depends on exactly how you are using this data. We might need to better understand exactly where the data are all coming from and exactly how you intend to use the data.

Hi kfbeal,

This looks like a Pivot Table answer using both dates and times are what might help. See the attached where I've done both. See if this gets you closer to your answer. If you get these frequently then a newer version of Excel and Power Query might be a better solution. BUT first see if Pivot Tables get you a good answer.

meter-totalization suggestions.xlsx

Try the attachment. we try to use LOOKUP to get the value

Hi kfbeal,

You still need to get all your days in a single table before you pivot. Learn more about Pivots and how to filter and group. See the attached with two more examples. Filter the charts and start learning more about Pivots. Yep - Excel has lots of tools in it.

Time-Of-Use-April-2020 Two Examples.xlsx

Ok, I was finally able to get some time to work on this from all of your help. I think I got some of the data part figured out for now just using Excel. Yes MarvinP, I finally got the data into a single table. The other was just for easy illustration side by side. In the file, you'll see I have actually 12 pivot tables on the same dataset for 4 different meters we need to totalize. This includes the KW and the KWH.

• Within each pivot table, is there a way to display the date of the Peak KW value so we know what date it occurred on like right below Meter and Type?

• I found out today, we need the 60 minute intervals as well. Is it possible to take an average of the 4 intervals of every hour such as :00, :15, :30 & :45 and display that through the pivot table? I'm assuming that calculation would need to be done through the data table & just displayed in the Pivot Table? Pivot Tables are pretty powerful in displaying data. If so, any easy way of doing that?

• Is it possible to enter the date for the Previous KWH reading once and it be used across all the pivot tables? The same goes for the Present KWH Reading?

Within each pivot table, is there a way to display the date of the Peak KW value so we know what date it occurred on like right below Meter and Type?

Assuming that this refers to pivot tables 8:11 then the following proposal may help.
1. List the meters in H2:H5
2. Populate I2:I5 using:

Formula:

Please Login or Register  to view this content.

3. Populate F2:F16146 using: =IF(AND(C2="KW",COUNTIFS(H$2:H$5,B2,I$2:I$5,D2)),D2,"")
4. The source of each of the afore mentioned pivot tables is modified to include column F
5. Peak Date is added to the values field.
Let us know if you have any questions.

I found out today, we need the 60 minute intervals as well. Is it possible to take an average of the 4 intervals of every hour such as :00, :15, :30 & :45 and display that through the pivot table? I'm assuming that calculation would need to be done through the data table & just displayed in the Pivot Table? Pivot Tables are pretty powerful in displaying data. If so, any easy way of doing that?

This could be done with the existing data as modeled on Sheet1.
1. Drag Meter and Type into the filters field
2. Drag Date into the Row field. It should display twice, once as Days and once as Date. Drag Days back into the filters field. Right click on the Dates (may be months) in the pivot table and select group > days and hours.
3. Drag Values into the values field. Use the value field settings to change from sum to average.
Let us know if you have any questions.

Since I don't know anything about querying Access databases and as you are using the 365 version of Excel then I would think about putting the data into a data model.
Perhaps the tutorial video linked below will help.
https://www.youtube.com/watch?v=aOQdxhLCuCA
Let us know if you have any questions.