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
|
|||
|
|||
unexpected result on average of 2 vlookup
"daredelsol" wrote in message
... Hello. I have two VLOOKUP formulas that give the right answer in two cells and then I can get an average of those two cells correctly by using AVERAGE. But, when I try to combine the two VLOOKUPs into one cell's AVERAGE formula I get an unexpected result. The area in the VLOOKUPs appear to not change when I am on the individual formula as shown he VLOOKUP(M2,TableData,6,FALSE) = 26 VLOOKUP(M2,TableData,7,FALSE) = 46 Average of both: AVERAGE(I3:I4) = 36 correct AVERAGE(VLOOKUP(M2,TableData,6,FALSE),(VLOOKUP(M2, TableData,7,FALSE)) = 19 incorrect Why? and What is wrong? Thanks!!! I cannot reproduce your problem exactly. There would be a difference between your two scenarios if one of the values returned from the vlookups was text rather than a number (the number 26 is not the same as the text "26"), as AVERAGE(I3:I4) would not convert text to a number whereas AVERAGE(VLOOKUP...,VLOOKUP...) would convert it. But unless your example is a simplification of the actual problem, this would not give a result of 19. Perhaps you have a typing error? I notice that you have 4 left brackets but only 3 right ones in the composite formula as quoted. What you can do is to look at the results of part of a formula in the formula bar. Select the cell containing the composite formula. Now in the formula bar, select VLOOKUP(M2,TableData,6,FALSE) and press F9. You will see the result replace this part of the formula in the formula bar, so you can check that it is 26. Press ESC to get out of this mode. Repeat for VLOOKUP(M2,TableData,7,FALSE) to check that it is 46. This may help you locate the problem. |
Thread Tools | |
Display Modes | |
|
|