Excel Formulas

[lintz]

I've got a spreadsheet from a person who isn't with the company anymore but the formulas are not behaving

Both screenshots contain the same formula (except for search phrase) but you'll notice the second 1 contains { } (start and end of formula). Screenshot 1 which doesn't contain { } doesn't display any results where as the formula surrounded by { } does.

Does anyone know how { } are automatically added to the start and end of a formula and why if the formula doesn't include them no result is displayed?

[lintz]

I don't know why {} around the formula works but found out how to add them.

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

[Ecniv]

Help> MS Excel help
Enter : array formula
Search it - read up on Create Array formula

Seems to do multiple calculations the way they should be without making lots of cells with functions.



Or just to confuse people with something they haven't used before.


Or here too

* Note: Pressing the Ctrl+Shift+enter only appears to work on entering the formula on the first attempt...

[anhn]

The person who wrote that array formula may have a good skill in Excel.
Array fomula is a very powerful way to calculate large and multiple range in Excel.

* Without knowing array formula, one may have to fill an extra column, let say M, in sheet Details from M2 to M11741, start at M2 with:
=IF(AND($G2="WCB1",$E2="Low",$L2>0),$L2,0)
or
=($G2="WCB1")*($E2="Low")*($L2>0)*$L2
then fill down to M11741
In other sheet, the equivalent formula should be:
=SUM(Details!$M$2:$M$11741)*100/C6

* The array formula
{=SUM((Details!$G$2:$G$11741="WCB1")*(Details!$E$2:$E$11741="Low")*(Details!$L$2:$L$11741>0)*Details !$L$2:$L$11741)*100/C6}
means
=(Details!$G2="WCB1")*(Details!$E2="Low")*(Details!$L2>0)*Details!$L2 + (Details!$G3="WCB1")*(Details!$E3="Low")*(Details!$L3>0)*Details!$L3 + ... + (Details!$G11741="WCB1")*(Details!$E11741="Low")*(Details!$L11741>0)*Details!$L11741
Of course you have no way to write this sum with 11740 terms.

* The above array formula can be written as a normal formula as below (for this case only):
=SUMPRODUCT((Details!$G$2:$G$11741="WCB1")*(Details!$E$2:$E$11741="Low")*(Details!$L$2:$L$11741>0)*( Details!$L$2:$L$11741))*100/C6

* Note: Pressing the Ctrl+Shift+enter only appears to work on entering the formula on the first attempt...

That is not true. To create an Array Formula, we cannot enter { } directly but we need to use Ctrl+Shift+Enter in Edit mode.
Ctrl+Shift+Enter will always work but you need to get in Edit mode first (double click the cell or select the cell then press F2).