I need a little advice from any excel guru’s. I have a list of dates and times in column “A” and a quantity in column “B”. This quantity is logged every hour and gives me 23 readings per day ( for some reason between 2300 and 23:59 the log misses and adds the total to the hour between 00:00 and 00:59) I want to put the average use in column “C”, the max use in “D” and the days total in “E”. I can use the functions to do this and was just selecting 4 days data and dragging the fill handle down to my last cell (6700 rows of data). but when i start to check i find that the data becomes misaligned with the days. I find that some random extra logs appear at strange times. The logging is done automatically. So what I am looking for is a formula that checks column “A” and only sums, avg and max calculations for the correct days. Hope that makes sense. Here is an example of the data with extra data at 13:11 and the subsequent misalignment of my functions.

11/12/2012 10:59_________8
11/12/2012 11:59_________10
11/12/2012 12:59_________5
11/12/2012 13:11_________10
11/12/2012 13:59_________10
11/12/2012 14:59_________10________(AVG)7.391304348_________(MAX)20_________(TOTAL)170
11/12/2012 15:59_________0

Any help is appreciated.

Turn the text dates into numerical dates. Then you can sort and work with them properly.

Hmmm, not sure over what period you want to apply the functions ? For the last 4 days ? And do you want to include or ignore the spurious readings ?

Use an IF function to put the readings into a separate column BUT only where the time ends 59. If a TEXT date you could do

=IF(RIGHT(A1,2)=”59″,A2,0)

where A1 is the text date value and A2 is the qty.

Doing the rest could involve a number of different tecniques. Counts, conditional sums
, offsets or maybe a pivot table. If I wasnt shattered after a work trip I could probably sit down with excel for 20minutes and give you a template.

Will RIGHT(xx) work with a date? I can’t check but I don’t think so.

Checking for the mins being 59 is a good idea bit you may need a different method.

You could just sort or filter the data for lines not ending 59 and delete them as a block.

When you do the MAX function have you adjusted for the double usage on the midnight reading.

is the average to be the mean of the values that are there for that day, or do you want the average hourly usage (which will be slightly differnet because you only have 23 points most days and some days you have 2 points in an hr).

Likewise what should max do if there are two data points in an hour? is max actually the sum of those? And how about if max is the first hour of the day which actually has ‘handover’ data from the last hour of the preceeding day?

Jfletch I am pretty sure it will work I think but only if the date is stored as text. if its not and is anumber then i agree he needs another method to achieve the same probably some mathematical function based around the value to establish the 59 for the logical test.

Create a new table of the times you want data at i.e. 59 minutes past the hour

Use VLOOKUP to populate this table from the original data

Do your sums on the new tidy data table

This method avoids the possibility of corrupting the data when you try to delete what you don’t need.

Thanks fr your replies s far. I dont actually want to delete the extra data points as they have valid data in the cells, just not sure what causes them to be logged, possibly a high value within a short period. What I am looking for is an easy way to put my daily totals against the correct group of cells no matter how many data poits there are on each day. Obviosly I can do this manually but there are 6700 cells, growing by 23 per day. As a temporary fix I will try to filter the extra points out. I will have to find a full solution tomorrow.

crmcc – assuming nothing is confidential if you send me the file, I can add a sheet to do what you want.
M

This isn’t going to help you right now but I tend to use FileMaker database for tons of stuff like this (its actually better in a number of ways).
If this is an ongoing thing then I think it would be a better solution as it would allow you to easily run calculations on certain sections of data – for instance it would be easy to simply ignore (not delete) any data that wasn’t supplied at 59 minutes past the hour.

you could use the “minute” function to determine if a time ends in 59 minutes;

=IF(MINUTE(B2)=59,C2,””)

where the date is in B2, and the data in C2. The date would need to be a date though, not text.

The average and the sum are relatively easy

If you have your date and time entered as a date (not text) then…

In coulmn C enter
=IF(MINUTE(A1)=59,TEXT(A1,”dd/mm/yyyy”),””)

Then to calculate the average for the day in column D enter
=IF(AND(HOUR(A1)=23,MINUTE(A1)=59),AVERAGEIF(C:C,C1,B:B),””)

To calculate the sum for that day in column E enter
=IF(AND(HOUR(A26)=23,MINUTE(A26)=59),SUMIF(C:C,C26,B:B),””)

This will calcuate the average and sum of all the values entered for that date at 59 mins past the hour and display it adjacent to the record taken at 23:59.

The max is a bit harder as there isn’t a MAXIF function in excel.

Edit:Maybe not clear but you need to fill down all of the formulas so they are in every row with data.

Thanks guys. Stoner UGM.

UGM back.

Having had a play with the original data the problem is getting array formula to discard the time element (decimal) from the date column when doing conditional sums.

Rather than put the summary calcs in the original data sheet I prefer to have them in a second sheet that, with just one reference, can then address the data sheet and bring back the summaries and so be reused very easily.

But I cant get the array formula to apply a QUOTIENT or INT function to the datetime array so that you can match a Criteria date to the datetime data.

I might have a go at building a date and time reference with minimal user inputs and use match and offset to do it instead…

crmcc, if you want to chuck the data to me I can give you the figures – should take about 5 mins.

If you use a database for this it will keep all your data for as long as you like….. I have databases with over 4.5 million records in that are way more complex.
I think it’s a solution you should look at. There’s also a 30 day demo of FileMaker available.

have sent a second version now that does it all on one page that can be copied to any number of new data work books for re-use.

In the end I used OFFSET, MATCH and INDIRECT.

In the end I used OFFSET, MATCH and INDIRECT

Go on then, spill the beans, we are all dying to know how.

I figured out how to do the MAXIF to fit in with my method above.

In column F enter
=IF(AND(HOUR(A26)=23,MINUTE(A26)=59),MAX(IF(C:C=C26,B:B)),””)

But when you confirm press CTRL + SHIFT + ENTER to tell excel its an array fomular. This will calculate the max.

If you wanted to you could put a separate sheet with every day in column A and the formulas above in B/C/D to create a ref all in one place. If you want me to put this in the real sheet the mail it to me. Mail in profile.

Pah ….. Excel sucks. Stoner it took you ages to work that out! I created a database that does exactly whats required in less than 5 mins – plus it can be used to select any range of dates and is effectively unlimited in file size.

crmcc – Im happy to send the FileMaker file over to you to play with if you download the FileMaker demo from here. It’s only 3 data fields and 3 summary calculations anyway. You can import your own data into it easily (in fact you could also set up an ODBC link to populate it automatically if you felt the need).

Excel sucks

Im happy to send the FileMaker file over to you to play with if you download the FileMaker demo from here. It’s only 3 data fields and 3 summary calculations anyway. You can import your own data into it easily (in fact you could also set up an ODBC link to populate it automatically if you felt the need

Are you on comission? The problem with using a database for simple things like this is a lot of what you wrote is gobledegook to non techys.

I’d agree excel sucks when it is used inappropriately but quick cacluations with not much data are what its for, and this is that. I did the sums in about 5 mins but had a load of time inbetween doing my actual work. Just as difficult to learn array fromulas as it is start from scratch on DB queries.

No commision, just been using it for about 20 years!
I’m a thicko and even I can use it – simple to pick up and you can chuck a DB together in mins, I do it for just the smallest of calculations.
You’re dead right about Excel being good for small amounts of temporary data – but from what I gathered crmcc could really do with a solution that will grow. He could add data to a DB every day for decades and it would still work great plus he could go back and look at/compare historic data with just a couple of clicks…. and FileMaker is compatible with pretty much every format out there and it runs on PC or Mac.