VBA for AverageIfs formula

[abonnette86]

I have a relatively large inventory spreadsheet where I have a column for calculated usage, and I need to generate average weekly usage based on multiple criteria rolling down the sheet. But the native AverageIfs() formula is so slow, and I need the sheet to work quickly, so VBA seems to be a better option to do the job.

Is there a way to replicate this in VBA and have it automatically run on cell change?

= IFERROR ( ROUNDUP ( AVERAGEIFS ( $J$2 : $J14670 , $A$2 : $A14670 , ">=" & $L14670 , $A$2 : $A14670 , "<=" & $A14670 , $B$2 : $B14670 , $B14670 , $C$2 : $C14670 , $C14670 ) , 0 ) , 0 )

I wouldnt mind referencing the whole column either, I limited the range to try saving calc time. Didnt work

 

[jasonb75]

This should do it, please test on a copy of your file, not the original.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Target
    Cells(c.Row, "M").Value = Evaluate("=IFERROR(ROUNDUP(AVERAGEIFS($J:$J,$A:$A,"">=""&$L" & c.Row & ",$A:$A,""<=""&$A" & c.Row & ",$B:$B,$B" & c.Row & ",$C:$C,$C" & c.Row & "),0),0)")
Next
End Sub

 

[abonnette86]

This should do it, please test on a copy of your file, not the original.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Target
    Cells(c.Row, "M").Value = Evaluate("=IFERROR(ROUNDUP(AVERAGEIFS($J:$J,$A:$A,"">=""&$L" & c.Row & ",$A:$A,""<=""&$A" & c.Row & ",$B:$B,$B" & c.Row & ",$C:$C,$C" & c.Row & "),0),0)")
Next
End Sub

Is there something else that should go along with this? The code seems to run fine but only on the 1st entry the 1st time. It wont calculate any changes after the 1st change

 

[jasonb75]

Is there something else that should go along with this?

I just realised the error that I made, the code will only update the result in the row that is changed, not all rows with matching records.
@DanteAmor provided an alternative to SUMIFS in a different thread which I think would be a better option if it can be adjusted to work with averages.

Do any of the cells / columns that the formula refers to also contain formulas?

 

[DanteAmor]

Hi Jason, Thanks for the mention, perhaps, it would be necessary to perform the calculations in memory to make the process faster.

Hi @abonnette86
IFERROR(ROUNDUP(AVERAGEIFS($J$2:$J14670,$A$2:$A14670,">="&$L14670,$A$2:$A14670,"<="&$A14670,$B$2:$B14670,$B14670,$C$2:$C14670,$C14670),0),0)
In your formula in the first criteria you are comparing dates against values of cell L.
Should it be against the same cell as column A?
Something like this:

IFERROR(ROUNDUP(AVERAGEIFS($J$2:$J14670,$A$2:$A14670,">="&$A14670,$A$2:$A14670,"<="&$A14670,$B$2:$B14670,$B14670,$C$2:$C14670,$C14670),0),0)

 

[abonnette86]

Hi Jason, Thanks for the mention, perhaps, it would be necessary to perform the calculations in memory to make the process faster.

Hi @abonnette86
IFERROR(ROUNDUP(AVERAGEIFS($J$2:$J14670,$A$2:$A14670,">="&$L14670,$A$2:$A14670,"<="&$A14670,$B$2:$B14670,$B14670,$C$2:$C14670,$C14670),0),0)
In your formula in the first criteria you are comparing dates against values of cell L.
Should it be against the same cell as column A?
Something like this:

IFERROR(ROUNDUP(AVERAGEIFS($J$2:$J14670,$A$2:$A14670,">="&$A14670,$A$2:$A14670,"<="&$A14670,$B$2:$B14670,$B14670,$C$2:$C14670,$C14670),0),0)

No what showed is correct - after analysis I have to paste values into a sheet I need to distribute. And I need the recipients to see the date range values being averaged by showing both MIN and MAX dates. My question simply put is can I recreate the formula in VBA or create a custom function in VBA to perform the same task, because the native formula just takes too long to calculate

 

[DanteAmor]

No what showed is correct

I don't understand, you are comparing dates against values, is that correct?

My question simply put is can I recreate the formula in VBA

To answer that, I need to understand the formula and if the formula has errors then the macro will be built with errors.

 

[jasonb75]

I don't understand, you are comparing dates against values, is that correct?

@DanteAmor there are 2 columns with dates, the method used in the original formula creates a date range for each row (dates between the date in L14670 and the date in A14670 which is a period of 21 days based on the screen capture but I'm assuming that this could be variable on other rows). Using your suggested edit to the formula you would only get equal dates (a date in column A can't be > than A14670 and < than A14670 so only the equal dates will be evaluated).

I can see what is needed, I just don't understand the dictionary code that you have used well enough to do the edits.

 

[abonnette86]

Jasonb75 is correct

 

[DanteAmor]

Thanks Jason.
I got it.
The problem started in that the OP image does not see the letters of the columns and I have to count one by one.

I prepare something to see if it helps the OP.