Index Match not following Order of Operations?

[Jambi46n2]

Hello,

I have two formulas that calculate the exact same numbers, however I'm getting two different results.

First one is as follows:

=('CONBUD - SUBS'!AF7-'CONBUD - SUBS'!BV7)*('CONBUD 1'!BA9/'CONBUD - SUBS'!BH7)

Translates to:
=(5727010-5703134.04249506)*(63710728.16/5727010)

Answer:
[TABLE="width: 275"]
<tbody>[TR]
  [TD="class: xl67, width: 275"]265,611 [/TD]
[/TR]
</tbody>[/TABLE]

The above answer is correct.

Now when I apply the exact same problem with an Index match as follows:

=INDEX($C7:$ACQ7,0,MATCH(ACS2,$C$2:$ACQ$2,0))
-
INDEX($C7:$ACQ7,0,MATCH(ACT2,$C$2:$ACQ$2,0))
*
INDEX('CONBUD 1'!N9:EO9,0,MATCH('CONBUD - SUBS'!ACS2,'CONBUD 1'!N1:EO1,0))
/
INDEX($C7:$ACQ7,0,MATCH(ACS2,$C$2:$ACQ$2,0))

which translates to:

=5727010
-
5703134.04249506
*
63710728.16
/
5727010

Answer being:
[TABLE="width: 275"]
<tbody>[TR]
  [TD="class: xl69, width: 275"]                                                                  (57,718,108)[/TD]
[/TR]
</tbody>[/TABLE]

The above answer here is incorrect. Any ideas on why the exact same problem is providing different answers?

Thank you much in advance.

 

[jtakw]

Hi,

PEMDAS, you're missing parentheses:

=(INDEX($C7:$ACQ7,0,MATCH(ACS2,$C$2:$ACQ$2,0))-INDEX($C7:$ACQ7,0,MATCH(ACT2,$C$2:$ACQ$2,0)))*(INDEX('CONBUD 1'!N9:EO9,0,MATCH('CONBUD - SUBS'!ACS2,'CONBUD 1'!N1:EO1,0))/INDEX($C7:$ACQ7,0,MATCH(ACS2,$C$2:$ACQ$2,0)))

 

[Jambi46n2]

Thank you for the prompt response!

Through some trial and error I was also able to get the correct answer by using SUM with before INDEX like so:

=SUM(INDEX($C7:$ACQ7,0,MATCH(ACS2,$C$2:$ACQ$2,0))
-
INDEX($C7:$ACQ7,0,MATCH(ACT2,$C$2:$ACQ$2,0)))
*
SUM(INDEX('CONBUD 1'!N9:EO9,0,MATCH('CONBUD - SUBS'!ACS2,'CONBUD 1'!N1:EO1,0))
/
INDEX($C7:$ACQ7,0,MATCH(ACS2,$C$2:$ACQ$2,0)))

I appreciate you!

 

[jtakw]

You Don't Need the SUM function at all. You just need the Brackets like I show you in Post # 2.
Take Out the SUM but leave the brackets in.