We help IT Professionals succeed at work.

# VBA to convert standard formula to array

on
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.
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Don't know about the macro but what version of excel are you using? If you have Excel 2007 or later then you can use AVERAGEIF which doesn't need to be "array entered"

regards, barry
CERTIFIED EXPERT
Commented:
You can try this macro for to take care of the current sheet.

Sub cvarray()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
If Left(cel.Formula, 12) = "=AVERAGE(IF(" Then cel.FormulaArray = cel.Formula
Next cel
End Sub
Finance Analyst
CERTIFIED EXPERT
Commented:
Alternative to using AVERAGE, also not needing array entering.

=SUM(\$A\$3:\$A\$800)/COUNTIF(\$A\$3:\$A\$800,"<>"0)

Thanks
Rob H
Finance Analyst
CERTIFIED EXPERT

Commented:
Slight typo, s/be:

=SUM(\$A\$3:\$A\$800)/COUNTIF(\$A\$3:\$A\$800,"<>0")

or

=SUM(\$A\$3:\$A\$800)/COUNTIF(\$A\$3:\$A\$800,"<>"&0)

Commented:
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,">0")
Most Valuable Expert 2012
Top Expert 2012
Commented:
Its been a few hours, so here's something that might help, though barry might have other ideas, hopefully this will help you get over your current "hump".....
-------------------------------------------------------------------------
=AVERAGEIF(A1:A7,A10:A16,">0")  doesn't have multiple critiera.

It is getting the average of A1:A7 where A10:A15 is > 0.  Not really sure what you were trying to do as you didn't mention what range needed to be averaged and which was the criteria, so clarity there would help.

In Excel 2007, the AVERAGEIFS however, DOES support multiple criteria.  Here's the syntax (abbreviated - from Excel Help):

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2â€¦)

so the first parameter is the range to average, the second is a range for criteria testing, the THIRD parameter is the test.  Then, if there are more criteria, you specify a new range for testing and then the parameter for testing against that range.

For example, if you want the Average of A1:A7, where A1:A7 values are > 0 and the B1:B7 values are > 10 at the same time:

=AVERAGEIFS(A1:A7,A1:A7,">0",B1:B7,">10")

Another example - might be what you're trying to do.

If you want the Average of A1:A7,A10:A16 where the values in those ranges are > 0, I don't believe you can do that with the AVERAGE function, but you can do it any number of ways.  Let's discuss it using SUMIF and COUNTIF

=(SUMIF(A1:A7,">0")+SUMIF(A10:A16,">0"))/(COUNTIF(A1:A7,">0")+COUNTIF(A10:A16,">0"))

If neither one of these are what you're trying to do, hopefully, you've learned a bit more about the AVERAGEIFS function.

In Excel, type

=AVERAGEIFS(

then hit ALT-F and the function help will come up - you can hit "help on this function" to get the full help.  Or just F1 and search for it.

Cheers,

Dave
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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:A7)

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,">0")

regards, barry

Commented:
Thanks again everyone for the great feedback. Have a nice weekend!