Ron - This formula is Outstanding -- For the FIRST Time EVER I used the menu
option Tools, Formula Auditing, Evalute Formula AND WOW!!! What a feature!!
One Question -- I would like to capture and Print out EACH of the Steps Into
a Print-Out so that I can Move away from the Computer and begin the 2-hour
process of studing each step. Can this be done using a VAB routine? I tries
highlighting each single step and Copying (Ctr+C) to paste into a sheet, but
such is not possible.
Can you assist me in nailing this thing down?
Much appreciated,
Jim May
I don't believe the Formula Evaluation wizard is accessible from VBA.
But here is a bit of what is going on:
ROW(INDIRECT(MIN(A1:A8)&":"&MAX(A1:A8)))
{39449;39450;39451}
creates an ordered array which includes all of the dates between the earliest
date and the latest date in the data. The dates are returned as serial numbers
(with Jan 1, 1900 = 1)
So the function: SUMIF(dates,{array of dates},production) will return an array
of the sums for each of the dates.
{212;150;180}
Applying the MAX function to that result returns the highest value.
212
Then MATCH tells us where in the array this occurred
1
and we use the INDEX function (against our generated array of dates) to tell
which date it is.
--ron