Alternative to AVERAGEIFS to calculate 2 columns with about 1 Million rows taking days

This is outside my experience, but I think you are probably asking Excel to do more than it's designed for. Especially with AVERAGEIFS and MAXIFS formulas.

I think you probably need a database program, or maybe the POWERQUERY add-in for Excel.

Fortunately, after 44+ hours the document saved and I was able to convert the formulas to values to avoid further headaches. Very happy about this. I guess I can set this as solved now, but would like some pointers if possible for the code below.

Originally Posted by David A Coop

This is outside my experience, but I think you are probably asking Excel to do more than it's designed for. Especially with AVERAGEIFS and MAXIFS formulas.

I think you probably need a database program, or maybe the POWERQUERY add-in for Excel.

I've only used POWERQUERY to merge a large number of files into a table but didn't consider using it for my other needs. Based on my searches I would have to create my own formulas to get the data that I am seeking since Excel functions are not available through POWERQUERY. I have some homework to do regarding databases and POWERQUERY. Thanks for the suggestions.

Originally Posted by protonLeah

Functions like Average and max are 'usually' used to summarize/compare data. You have "millions" of rows of data so, in columns E & F you have millions of cells each one calculating avgs and max's on millions of cells. How many actual unique dates to you really need to analyze at one time?

I realized it wasn't efficient to do it this way once each cell was looking at millions of rows. Each type (A, B, C, etc) has nearly a year and half of data. I need all these values available on the fly, so I don't have an alternative. I am thinking a VBA macro could work with For and IFs loops to look at and compare if B2 and C2 remain the same one row below and if true this would develop a range for values I need to calculate in column D. Once the range is created it would calculate the average and max for this small range, and continue through the workbook. This isn't my strong suit, so it'll take me a bit to develop.

So far this test code works to select my target column. I am just not sure how to create the range I need using another For or If loop.

Please Login or Register  to view this content.

mrbarba for your source you can use

Please Login or Register  to view this content.

that decreased calculation
For maxif also
But i would like to use PivotTable and get data by GETPIVOTDATA

Originally Posted by Bo_Ry

I would use Pivot Table, with a few click
But LF need to add formula
=K2/L2/24

or Power Pivot by Insert Pivot and check add to data model
Power Pivot ribbon New Measure

Average
=AVERAGE(Range[Value])

Max
=MAX([Value])

LF
=DIVIDE([Average],[Max])/24

I was trying to do this with by adding a PivotTable using the Data Ribbon tab, but wasn’t able to. I didn’t know about the Power Pivot COM Add-in. Thank you for providing this information Bo_Ry! This would have saved me so much time. I have much to research now.

Originally Posted by BMV

mrbarba for your source you can use

Please Login or Register  to view this content.

that decreased calculation
For maxif also
But i would like to use PivotTable and get data by GETPIVOTDATA

This is good because it can save excel from looking at millions of cells (based on my data) which will reduce calculation time. Thanks BMV.
Based on your guys feedback the PivotTable is the best solution, specially now that I now of the Power Pivot.