If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Wrong results from MATCH function
Softwa Excel 2007 SP1
I'm using the FV function to compare future returns on investments. The results of the six scenarios in row 36 are as follows: C:36 $606,174.11 D:36 $549,893.90 E:36 $606,776.86 F:36 $531,553.59 G:36 $129,076.61 H:36 $121,795.72 In J:36 I have the function "=MAX(C36:H36)" which correctly outputs "$606,776.86" as the largest value. In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs "3" as the result. Below are the results of the same scenarios in row 37 (one additional payment/investment period): C:37 $609,686.10 D:37 $551,850.46 E:37 $608,361.51 F:37 $532,382.33 G:37 $133,293.94 H:37 $125,276.10 The same functions were included in columns J and K. The MAX function in J:37 outputted the correct result: $609,686.10 (column C). However, the MATCH function in K:37 incorrectly outputted "6" as the result. On all subsequent rows column "C" has the highest return, with the MAX function giving me the correct result and the MATCH function returning "6" to me. The same thing happens whether I use the FV function in columns C-H or copy and paste just the values; the MATCH function gives me bad information. I've included the results from rows 36 & 37 below for ease of 'copy and paste'. Row 36: $608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29 Row 37: $609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10 Any help would be greatly appreciated! |
#2
|
|||
|
|||
Wrong results from MATCH function
You should use an EXACT match,
ie with the match type parameter set to FALSE or zero Instead of in K36: "=MATCH(J36,C36:H36)" Use in K36: =MATCH(J36,C36:H36,0) voila? punch it here, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Imladrian" wrote: Softwa Excel 2007 SP1 I'm using the FV function to compare future returns on investments. The results of the six scenarios in row 36 are as follows: C:36 $606,174.11 D:36 $549,893.90 E:36 $606,776.86 F:36 $531,553.59 G:36 $129,076.61 H:36 $121,795.72 In J:36 I have the function "=MAX(C36:H36)" which correctly outputs "$606,776.86" as the largest value. In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs "3" as the result. Below are the results of the same scenarios in row 37 (one additional payment/investment period): C:37 $609,686.10 D:37 $551,850.46 E:37 $608,361.51 F:37 $532,382.33 G:37 $133,293.94 H:37 $125,276.10 The same functions were included in columns J and K. The MAX function in J:37 outputted the correct result: $609,686.10 (column C). However, the MATCH function in K:37 incorrectly outputted "6" as the result. On all subsequent rows column "C" has the highest return, with the MAX function giving me the correct result and the MATCH function returning "6" to me. The same thing happens whether I use the FV function in columns C-H or copy and paste just the values; the MATCH function gives me bad information. I've included the results from rows 36 & 37 below for ease of 'copy and paste'. Row 36: $608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29 Row 37: $609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10 Any help would be greatly appreciated! |
#3
|
|||
|
|||
Wrong results from MATCH function
You have not used the third argument with MATCH. When this is omitted the
table being looked must be sorted (ie: in order) It just happened to give you the right result in the first set of data Use "=MATCH(J36,C36:H36,0)" ----- the 0 stipulates you want an exact match and it must be used then the table is unsorted. If you search Help with the word "MATCH" you will be able to confirm what I am telling you best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Imladrian" wrote in message ... Softwa Excel 2007 SP1 I'm using the FV function to compare future returns on investments. The results of the six scenarios in row 36 are as follows: C:36 $606,174.11 D:36 $549,893.90 E:36 $606,776.86 F:36 $531,553.59 G:36 $129,076.61 H:36 $121,795.72 In J:36 I have the function "=MAX(C36:H36)" which correctly outputs "$606,776.86" as the largest value. In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs "3" as the result. Below are the results of the same scenarios in row 37 (one additional payment/investment period): C:37 $609,686.10 D:37 $551,850.46 E:37 $608,361.51 F:37 $532,382.33 G:37 $133,293.94 H:37 $125,276.10 The same functions were included in columns J and K. The MAX function in J:37 outputted the correct result: $609,686.10 (column C). However, the MATCH function in K:37 incorrectly outputted "6" as the result. On all subsequent rows column "C" has the highest return, with the MAX function giving me the correct result and the MATCH function returning "6" to me. The same thing happens whether I use the FV function in columns C-H or copy and paste just the values; the MATCH function gives me bad information. I've included the results from rows 36 & 37 below for ease of 'copy and paste'. Row 36: $608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29 Row 37: $609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10 Any help would be greatly appreciated! |
#4
|
|||
|
|||
Wrong results from MATCH function
If the third argument of MATCH() is omitted, it is assumed to be 1. If
match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value and so to return the correct value the lookup values should be in sorted order.....If match_type is 0 MATCH finds the first value that is exactly equal to lookup_value and the array can be in any order. If this post helps click Yes --------------- Jacob Skaria "Imladrian" wrote: Softwa Excel 2007 SP1 I'm using the FV function to compare future returns on investments. The results of the six scenarios in row 36 are as follows: C:36 $606,174.11 D:36 $549,893.90 E:36 $606,776.86 F:36 $531,553.59 G:36 $129,076.61 H:36 $121,795.72 In J:36 I have the function "=MAX(C36:H36)" which correctly outputs "$606,776.86" as the largest value. In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs "3" as the result. Below are the results of the same scenarios in row 37 (one additional payment/investment period): C:37 $609,686.10 D:37 $551,850.46 E:37 $608,361.51 F:37 $532,382.33 G:37 $133,293.94 H:37 $125,276.10 The same functions were included in columns J and K. The MAX function in J:37 outputted the correct result: $609,686.10 (column C). However, the MATCH function in K:37 incorrectly outputted "6" as the result. On all subsequent rows column "C" has the highest return, with the MAX function giving me the correct result and the MATCH function returning "6" to me. The same thing happens whether I use the FV function in columns C-H or copy and paste just the values; the MATCH function gives me bad information. I've included the results from rows 36 & 37 below for ease of 'copy and paste'. Row 36: $608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29 Row 37: $609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10 Any help would be greatly appreciated! |
Thread Tools | |
Display Modes | |
|
|