R
Rationale01
Hi all
After sorting a table of data based on a field, in this instance “Name†the
array formulas which compare values within said rows against fixed values
using ={SUM(IF… are still looking up values within the newly sorted row
location as if they were $’d, but outputting the answer in the pre-sorted
row. I have not $’d them to allow filling down of the formulas.
Confused?
E.g. Row 2 is named “zebra†and is currently sorted by “quantity†of Zebras
(lowest to highest) with an array formula in row 2, looking up row 2
conditions against a $’d range of values, outputting a solution in row 2.
I now sort by alphabetically by “Name†placing “Zebra†at the bottom in row
654 yet in the array formula of Row 2, it is now looking at row 654 and
outputting the solution in row 2!?
Basically it is outputting the correct solution for “Zebra†conditions but
placing it with the wrong field, Row 2, ie for Aardvark.
This is not happening for all other non-array formulas I am using, and this
seems to go against Excel’s functionality for allowing data sorts with
formulas. Is there something I’m missing or am overlooking in my use of array
formulas?
Appreciate your help.
After sorting a table of data based on a field, in this instance “Name†the
array formulas which compare values within said rows against fixed values
using ={SUM(IF… are still looking up values within the newly sorted row
location as if they were $’d, but outputting the answer in the pre-sorted
row. I have not $’d them to allow filling down of the formulas.
Confused?
E.g. Row 2 is named “zebra†and is currently sorted by “quantity†of Zebras
(lowest to highest) with an array formula in row 2, looking up row 2
conditions against a $’d range of values, outputting a solution in row 2.
I now sort by alphabetically by “Name†placing “Zebra†at the bottom in row
654 yet in the array formula of Row 2, it is now looking at row 654 and
outputting the solution in row 2!?
Basically it is outputting the correct solution for “Zebra†conditions but
placing it with the wrong field, Row 2, ie for Aardvark.
This is not happening for all other non-array formulas I am using, and this
seems to go against Excel’s functionality for allowing data sorts with
formulas. Is there something I’m missing or am overlooking in my use of array
formulas?
Appreciate your help.