CODEWITHSUNDEEP
arraysexcelexcel-formulaarray-formulas
Ross Symonds
Ross Symonds

Reputation: 87

Understanding the Results of an Array Formula

I am trying to mend / understand a workbook an old employee made. I have used the Evaluate Formula feature but am still really struggling to understand the formula.

The formula is - 

=IF(INDEX(Grp2.Customer.Table,Grp2.Cust.Numb,1)<INDEX(Year,1,Year.Numb),IF(INDEX(Grp2.Customer.Table,Grp2.Cust.Numb,2)>IF(Year.Numb=1,Laun.Yr.Start,INDEX(Year,1,Year.Numb-1)),1,0),0)

E65 = 1, E66 = 0.

But what I am seeing when I am evaluating the formula, suggests to me that E65 and E66 should be the same.

Cell E66

enter image description here enter image description here

enter image description here enter image description here

Cell E65

enter image description here

enter image description here enter image description here

Upvotes: 2

Views: 83

Answers (2)

user11982798
user11982798

Reputation: 1908

Array formula is to process data in array or range:

To distinguish array formula and normal formula is sign of {} in your formula, for example if in normal formula = A1:A5 & B1:B5 so in array formula will be {= A1:A5 & B1:B5} if you look it in formula bar

For normal formula = A1:A5 & B1:B5 will give you only A1 & A5 but in array formula will give you based on your range follow to the array formula, so if your array formula is stand in cell C1, your result in C1 is = A1 & B1 but if your range for the array formula are in C1: C3, so in C1 yor result will be A1 & B1, in C2 will A2 & B2 and so on. But if you check in each formula in c1 upt c3 they will be same.

To make array formula, you select range where will you put the array formula, and in formula bar you put your formula for example = A1:A5 & B1:B5 and follow with Ctrl+Shift+Enter.

Upvotes: 2

Jeffrey Knight
Jeffrey Knight

Reputation: 51

I can't help you other than to simplify your formula (below). If you can I suggest - un-nesting those IF statements into other cells or defined formulas/names - use Alt-Enter to put line breaks in very-long-formulas - use the Excel Comments feature to 'comment' on the formula and why it's written the way it is

Good luck with your code decoding!

=IF(A<B,
   then IF(C > IF(D = 1, 
                  then E,
                  else F)
           then 1, 
           else 0),
   else 0)

Variable A = INDEX(Grp2.Customer.Table,Grp2.Cust.Numb,1)
Variable B = INDEX(Year,1,Year.Numb
Variable C = INDEX(Grp2.Customer.Table,Grp2.Cust.Numb,2)
Variable D = Year.Numb
Variable E =  Laun.Yr.Start
Variable F = INDEX(Year,1,Year.Numb-1)

Upvotes: 1

Related Questions