Array formula Explained For Everyone (this is a reference sheet)

People in accounting and scientific research won't learn how to use Array formulae (the curly brackets), because it is not an auditable process; firstly, because under various conditions you can lose the curly brackets somewhere and there's no alert to warn you of this, and secondly because you are working in a virtual space that cannot be visually evaluated.

For those who want to rearrange data repeatedly in the future, or are working across workbooks/sheets, replace the array formula with actual values once the operation is complete. For those looking to develop scalable data analysis or reporting tools, welcome to the labour force!

Arrays: takes data, sorts it virtually, calculates virtually; eliminates need to build multiple sheets of data and let's you skip steps.

Example:
Potatoes, along with other items, are sold at different prices on each day: A=Price, B=Item Name, C=Number of sales Per Day:

"=sum(if($b$1:$b$20="potatoes",$c$1:$c$20)*if($b$1:$b$20="potatoes",$a$1:$a$20) --> Returns error without curly brackets because you are not giving it permission to work virtually; so using curly brackets...
"{=sum(if($b$1:$b$20=,$c$1:$c$20)*if($b$1:$b$20="potatoes",$a$1:$a$20))} --> Great Success! Remove after results and computation: copy and paste values!"[/B]

Array Formula are resource intensive!

Alternatively, you would have to build a whole lot of stuff to get this done; maybe one or two tables. And it's fine if you do that - at least your solution methodology can be ported effortlessly to other work.
End of Example

Fzolf. May this be found by everyone and every time, until the end of time!

So take, these broken wings...texas potato.xlsx

"=sumif($b$1:$b$20="potatoes",$c$1:$c$20)*$a$1:$a$20) --> Returns error without parenthesis because you are not giving it permission to work virtually"
"{=sum(if($b$1:b$20="potatoes",$c$1:$c$20)*$a$1:$a$20)} --> Great Success! Remove after results and computation: copy and paste values!"

First, both of your formulas have errors.

Second, why would you destroy the design after getting one set of results? If the price of potatoes changes every day, you've locked in the result for some unknown particular day, which defeats every conceivable purpose of writing formulas in the first place.

@frankzolf

There were errors in your formulas (before you edit them)

I don't understand the purpose of this thread; also, in the wrong subforum.

Frankzolf, can I ask what the purpose of your thread here is?

It appears that your formulas contain errors, and, as has already been pointed out to you, your logic for replacing valid (and, despite your eroneous claim - very auditable) formulas with their values, is most certainly misplaced.

Just because a user could unknowingly just enter an array (instead of CSE), does not make them any more unworthy than a manual-shift car to a driver that has always driven an automatic.

So, I ask again, what is the purpose of this thread?

Dear FDibbins,

If you had to apply array formula to a large dataset and an individual with access to it clicks in the cell, the curly brackets do in fact disappear. there are no warnings that this error has occurred; the results undermine the integrity of the field and it is crucial that people are made well aware of these risks.

We battle everyday with integrity issues, this post merely attempts to be a clear, educational outline of the array formula - something that is difficult to find anywhere online. please accept that this has it's utility. If you do not agree and since I can find no reason why it's purpose and functionality do not align with the values of this forum, I have to ask: do you want me to take it down?

And by the way, the mass rollout of automatic transmissions in automobiles is not simply a joke, it actually reduces aggregate fuel consumption and unwanted 'wear and tear' of expensive, resource intensive vehicles. Would you trust a policeman to drive a 28-gear truck? he's not trained to, but yet baboons are allowed to do data capture. I know this forum has a higher purpose and I aim to meet this challenge and contribute where I see a deficit.

Regards
Fzolf
actuary

I have pretty much already covered this point you make (repeatedly)...

If you had to apply array formula to a large dataset and an individual with access to it clicks in the cell, the curly brackets do in fact disappear

Just because a user could unknowingly just enter an array (instead of CSE), does not make them any more unworthy than a manual-shift car to a driver that has always driven an automatic.

And to your claim that...."We battle everyday with integrity issues, this post merely attempts to be a clear, educational outline of the array formula" I hardly see how having a number/value in a cell - which you would have NO way of defending in any way, shape or form other than to say"well I copied it there from some formula", is anywhere close to having a formula that you cen explain and step through?

Your comment regarding why automatic transmissions are prevelent, is totally meaningless to my comparison of arrays to manual transmissions
"blaming array errors on operator inputs is akin to blaming the inability of a driver to drive a manual-shift car to a driver that has always driven an automatic"

now - nothing. I suggest you mark the thread solved and leav it at that

It's amazing what can be done with formulas and the array formulas are even more amazing, but a formula in a cell can always be erased or altered - that's why Excel has so many security protocols.

Yet many organizations and bosses will not allow macros even though they are "neater".

The actuarial exams are, self admittedly, graded subjectively - but that could just be sour grapes

I've always thought that SAP was well named