VBA to convert standard formula to array
I'm looking for a macro that will convert every formula in a list of specific worksheets that has a standard formula that starts with =AVERAGE(IF
The end result would look something like this: {=AVERAGE(IF($A$3:$A$800 <>0,$A$3:$A$800))}
You can see that I'm trying to avoid clicking [Ctrl][Shift][Enter] to convert this into an array formula.
The end result would look something like this: {=AVERAGE(IF($A$3:$A$800 <>0,$A$3:$A$800))}
You can see that I'm trying to avoid clicking [Ctrl][Shift][Enter] to convert this into an array formula.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
These are all good solutions. Thanks!
Barry, I didn't know about the AVERAGEIF function in 2007. I tried it out, and it works very nicely. However, it fails to work with a formula like this that has multiple criteria. Any suggestions on how to modify?
=AVERAGEIF(A1:A7,A10:A16,"
Barry, I didn't know about the AVERAGEIF function in 2007. I tried it out, and it works very nicely. However, it fails to work with a formula like this that has multiple criteria. Any suggestions on how to modify?
=AVERAGEIF(A1:A7,A10:A16,"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note that the order of the arguments in AVERAGEIF/AVERAGEIFS can be confusing as with SUMIF/SUMIFS
because with both of these the "IFS" version has the range to sum/average at the start whereas the IF versions have criteria range followed by criteria followed by sum/average range.
For example, if you want to average A1:A7 when the corresponding value in A10:A16 is >0 then the syntax for AVERAGEIF is like this
=AVERAGEIF(A10:A16,">0",A1
you wouldn't usually use AVERAGEIFS for a single criterion but if you did then this version gives an identical result
=AVERAGEIFS(A1:A7,A10:A16,
regards, barry
because with both of these the "IFS" version has the range to sum/average at the start whereas the IF versions have criteria range followed by criteria followed by sum/average range.
For example, if you want to average A1:A7 when the corresponding value in A10:A16 is >0 then the syntax for AVERAGEIF is like this
=AVERAGEIF(A10:A16,">0",A1
you wouldn't usually use AVERAGEIFS for a single criterion but if you did then this version gives an identical result
=AVERAGEIFS(A1:A7,A10:A16,
regards, barry
ASKER
Thanks again everyone for the great feedback. Have a nice weekend!
=SUM($A$3:$A$800)/COUNTIF(
or
=SUM($A$3:$A$800)/COUNTIF(