Min/Max Average based upon date

**hanjimono** · 03-18-2012, 01:13 PM

Hello Everyone!

Tried searching the forums but came up dry, hope I didn't miss the discussion!

Please bear with me during my description of the problem...

I have a column of numbers (referring to calendar dates, let's say they are A2 through A33). In B column, I have average formulas pulling data from another sheet. In C I have the MIN formula to get the min of that day and in D the MAX. I believe I got the MIN/MAX set up correctly as well as having the formula check if today's date matches the number in column A and if so, calculate the numbers. The issue I'm having is retain those averages once tomorrow comes. Here are the formulas I'm using:

Please Login or Register  to view this content.

Thank you for any help you may be able to provide!

**Richard Buttrey** · 03-18-2012, 01:28 PM

Hi,

Well (trivially) you could just copy and paste special values before tomorrow comes.

Of course you would no longer have any formulae, but I'm not clear as to what is your ultimate aim. Are you trying to retain a history of each day as you progress through the future, just the last day or some other requirement?

Regards

**hanjimono** · 03-18-2012, 01:32 PM

That is exactly what I suggested, but of course my boss is lazy and he wants this done automatically!

Yes, I'm trying to retain all the data for the entire month.

**Richard Buttrey** · 03-18-2012, 02:05 PM

Hi,

In that case you will need a macro. You need to determine what event should kick off this process and since the results will change every day the file is first opened the logical event is the Workbook_Open event. So go to the Visual Basic Environment and in the Workbook Object Open event enter the following macro.

Please Login or Register  to view this content.

In B1 of the worksheet enter the formula =TODAY(). This is required to prevent the updating if the workbook is opened more than once on the same day.

The macro inserts four new columns and then copies the values from B1:E33 to F1. If the workbook is opened again on the same day the If test which compares B1 to F1 will result in False and hence the macro will not run.

Hope this helps.

**hanjimono** · 03-21-2012, 07:13 AM

Hey I was waiting to thank you until I could try it out and see if it performed to my boss' specifications... well of course he changed his mind and went a different route which I was able to help him with so I just want to thank you very much for your time and effort helping me out!