Excel 2010 f9 formula debugging and evaluation

[DenniBrink]

Hi! Writing Excel 2010 formulas is trial and error experimentation. The Function Argument dialog box is helpful and so is the tool Formula Auditing Evaluate Formula option in writing Excel formulas. However, none of those choices, including the F9 key, fully reveal the formula argument responses. Is there a better way?

 

[Aladin Akyurek]

What is the formula you wanted to evaluate with F9?

 

[DenniBrink]

Thank you for your response. I am evaluating the formula Index(B8:B33,SMALL(IF(C8:C33=G7,ROW(B8:B33)),ROW(1:1))-1,1)) CSE.
I now am having greater success with the Formula Auditing Evaluate Formula option. I can now see the conditional list of True and False and how the Row function identifies the rows 8 - 33. Confused on how the Row(1:1)-1 argument mathematically bypasses the duplicates and proceeds downward to the next row.

 

[Aladin Akyurek]

Thank you for your response. I am evaluating the formula Index(B8:B33,SMALL(IF(C8:C33=G7,ROW(B8:B33)),ROW(1:1))-1,1)) CSE.
I now am having greater success with the Formula Auditing Evaluate Formula option. I can now see the conditional list of True and False and how the Row function identifies the rows 8 - 33. Confused on how the Row(1:1)-1 argument mathematically bypasses the duplicates and proceeds downward to the next row.

This formula is essentially wrong. See your other thread for the correct formula where you are asking for a "sublist".

F9 will show you the correct evaluation. ROW(B8:B33) evaluates to 8,9,10,...,33, which F9 will display. Note the row number of B8 is 8, and so on.