Change array to formula

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Can this array be changed to a formula?
Column A: ={IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")}
Thank you!

Cell Formulas
RangeFormula
A24:A32A24=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")
B24:B32B24=SUM(SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
C24:C32C24=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A$24:$A$32),"")
B33B33=SUM(B24:B32)
C33C33=IFERROR(AVERAGE(C$24:C$32),"")
D33D33=AVERAGE(D$24:D$32)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
That is a formula ;)
What version of Excel are you using?
Please change your account details to show this, as it affects what functions you can use.
 
Upvote 0
That is a formula ;)
What version of Excel are you using?
Please change your account details to show this, as it affects what functions you can use.
2019 Office
Its entered as an Array. My excel for this worksheet is very slow. Im just assuming it the Array formula? No other worksheets are this slow.
 
Last edited:
Upvote 0
An array formula for that small a range shouldn't be any problem, but a non-array version is
=IFERROR(INDEX($B$9:$B$20, AGGREGATE(15,6,(ROW($B$9:$B$20)-ROW($B$9)+1)/(ISNA(MATCH($B$9:$B$20,A$23:A23,0))),ROWS(A$24:A24))),"")

You formula in B can be done like
=SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
no need for the sum function

also for column C if you are trying to get the average of col L if B9:B20 is A24 use
=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A24),"")
filled down
 
Upvote 0
2019 Office
So I can remove the brackets, to act a formula?
Cell Formulas
RangeFormula
A24:A32A24=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")
B24:B32B24=SUM(SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
C24:C32C24=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A$24:$A$32),"")
B33B33=SUM(B24:B32)
C33C33=IFERROR(AVERAGE(C$24:C$32),"")
D33D33=AVERAGE(D$24:D$32)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B33:D33Cell Value<0textNO
B33:D33Cell Value>0textNO

An array formula for that small a range shouldn't be any problem, but a non-array version is
=IFERROR(INDEX($B$9:$B$20, AGGREGATE(15,6,(ROW($B$9:$B$20)-ROW($B$9)+1)/(ISNA(MATCH($B$9:$B$20,A$23:A23,0))),ROWS(A$24:A24))),"")

You formula in B can be done like
=SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
no need for the sum function

also for column C if you are trying to get the average of col L if B9:B20 is A24 use
=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A24),"")
filled down
I've made the changes you mentioned. Works fine. Thank you.
I will restart Excel and see if the speed has risen.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Cell Formulas
RangeFormula
A24:A32A24=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")
B24:B32B24=SUM(SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
C24:C32C24=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A$24:$A$32),"")
B33B33=SUM(B24:B32)
C33C33=IFERROR(AVERAGE(C$24:C$32),"")
D33D33=AVERAGE(D$24:D$32)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B33:D33Cell Value<0textNO
B33:D33Cell Value>0textNO


I've made the changes you mentioned. Works fine. Thank you.
I will restart Excel and see if the speed has risen.
I did a copy/paste to new worksheet, and its performing normally, not like before.
Maybe it didn't perform correctly because I had links to other worksheets that were either renamed or deleted?
It might have been searching for those worksheets and couldn't find them, continuously looping, causing the delay??
Now I'm a Happy Camper!! thx for your input.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top