abonnette86
New Member
- Joined
- Feb 21, 2020
- Messages
- 10
- Office Version
- 2013
- Platform
- Windows
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
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