Error calculating the difference of 2 numbers using if stmt with index match

[kgardner]

Hello,

I have a 3 column data table with cost centers [CC], transaction codes [TC], and dollar amounts [AMT] and I am trying to calculate the difference between debits [TC = 081] and credits [TC = 010] for each cost center. I put the cost centers in column F and used the formula below.

I used the formula =IF(Table[CC]=f2, index(table[amt],match["081", table[tc],0)) - index(table[amt], match("010", table[tc],0)), "error")

It works for cell f2, cc = 10, but does not work for cc 20,30,40 or 50. Any ideas?

Thanks,

KG

 

[joeu2004]

I used the formula =IF(Table[CC]=f2, index(table[amt],match["081", table[tc],0)) - index(table[amt], match("010", table[tc],0)), "error")

It works for cell f2, cc = 10, but does not work for cc 20,30,40 or 50. Any ideas?

What does "does not work" mean? Specifically what happens: an Excel error like #N/A? Or does it return "error", indicating that table[cc]=f2 is false?

Note that you match the string "081" and "010". I wonder if table[tc] contains numeric 81 and 10, at least in some cases.

If these comments do not help, I suggest that you upload an example Excel file that demonstrates the problem to a file-sharing website, then post the public/share URL in a response here.

 

[kgardner]

When I say 'does not work,' I mean that for cells g3 to g6 the value "error" from my IF formula is returned. The same formula is used in g2, but it correctly applies the index match formula and provides the difference between the AMTs associated with various 081 and 010 figures.

I will post a worksheet for you to review. What is a website I can use to provide a link?

 

[joeu2004]

When I say 'does not work,' I mean that for cells g3 to g6 the value "error" from my IF formula is returned.

If the formula returns "error", the problem is with the comparison table[cc]=f2. It has nothing to do with your INDEX/MATCH expressions.

I will post a worksheet for you to review. What is a website I can use to provide a link?

Here are some (all free):

Dropbox: http://dropbox.com
Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows OneDrive: http://onedrive.live.com

 

[kgardner]

Here is a link to the file

https://www.dropbox.com/s/4jziy52s5nqsszh/IF Index Match Help.xlsx?dl=0

 

[joeu2004]

Here is a link to the file
https://www.dropbox.com/s/4jziy52s5nqsszh/IF Index Match Help.xlsx?dl=0

Select one of the "error" cells (e.g. G3), then click on Formulas, Evaluate Formula on the toolbar, and click on Evaluate to step through the formula evaluation.

The problem should become obvious: in G3, table[cc] is 10, but F3 is 20. In G5, table[cc] is 20, but F5 is 50.

The problem is: each reference to table[cc] returns the corresponding value in the column labeled "CC". That is, look straight across to the left.

Table[cc] has multiple rows with 10, but there is only one 10 in column F. So the two columns will become out of sync.

It is unclear what you are trying to do in columns F and G; it is unclear which table[cc] with value 10 you want to refernce.

If you explain in English what you want to achieve in columns F and G, someone might be able to tell you how to redesign the formulas in column G.

 

[kgardner]

Joeu - Thank you for looking into this.

I want the formula in Column G to search the table by CC based on the number in column F, and find the difference in AMT between the TC 081 and TC 010 associated with each CC.

Example:
The formula in G2 looks in the table for CC = 10, then finds the difference between the AMT 5267 associated with TC 081 and the AMT 5267 associated with TC 010 to produce the value of 0.

I did the calculations manually to show you what I am looking for. I want to be able to do this as a formula so I can update the figures monthly for a report.

Here is the new spreadsheet: https://www.dropbox.com/s/4jziy52s5nqsszh/IF Index Match Help.xlsx?dl=0

 

[joeu2004]

I want the formula in Column G to search the table by CC based on the number in column F, and find the difference in AMT between the TC 081 and TC 010 associated with each CC.

Enter the following into G2 and copy down through G18:

=SUMIFS(table[AMT],table[CC],F2,table[TC],81) - SUMIFS(table[AMT],table[CC],F2,table[TC],10)