VLOOKUP using a cell calculated with NOW returns Error
I am calculating the present age of people, then by their age, I am looking
them up in a rate chart based on their age. I calculate their age by NOW-D.O.B. which works fine. (see actual cell contents below) Then, I use the cell that contains the age result in a VLOOKUP cell to determine their rate. It works perfectly for exactly 3 people, then it returns an error #REF! because, I have deduced, that it doesn't like the fact that the age is constantly changing each time the calculation is done. Why does it work for 3 people and then stop? More importantly, is there a way i can just take the "value" of the age cell into the VLOOKUP, rather than pulling the dynamic number into the function? Here's my VLOOKUP just for reference: =(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates! $B$2:$B$74))*M3 where F3 is the calculated AGE and M3 is a constant. Here's my present AGE calculation for reference (that is somehow considered in the above VLOOKUP): =IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, " ",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)) Where $A$1 = NOW any help gratefully acknowleged and appreciated ! |
Don't use NOW. Try TODAY().
************ Anne Troy www.OfficeArticles.com "Chris Berding" wrote in message ... I am calculating the present age of people, then by their age, I am looking them up in a rate chart based on their age. I calculate their age by NOW-D.O.B. which works fine. (see actual cell contents below) Then, I use the cell that contains the age result in a VLOOKUP cell to determine their rate. It works perfectly for exactly 3 people, then it returns an error #REF! because, I have deduced, that it doesn't like the fact that the age is constantly changing each time the calculation is done. Why does it work for 3 people and then stop? More importantly, is there a way i can just take the "value" of the age cell into the VLOOKUP, rather than pulling the dynamic number into the function? Here's my VLOOKUP just for reference: =(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates! $B$2:$B$74))*M3 where F3 is the calculated AGE and M3 is a constant. Here's my present AGE calculation for reference (that is somehow considered in the above VLOOKUP): =IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, " ",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)) Where $A$1 = NOW any help gratefully acknowleged and appreciated ! |
On Sat, 20 Aug 2005 18:40:01 -0700, "Chris Berding"
wrote: I am calculating the present age of people, then by their age, I am looking them up in a rate chart based on their age. I calculate their age by NOW-D.O.B. which works fine. (see actual cell contents below) Then, I use the cell that contains the age result in a VLOOKUP cell to determine their rate. It works perfectly for exactly 3 people, then it returns an error #REF! because, I have deduced, that it doesn't like the fact that the age is constantly changing each time the calculation is done. Why does it work for 3 people and then stop? More importantly, is there a way i can just take the "value" of the age cell into the VLOOKUP, rather than pulling the dynamic number into the function? Here's my VLOOKUP just for reference: =(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates !$B$2:$B$74))*M3 where F3 is the calculated AGE and M3 is a constant. Here's my present AGE calculation for reference (that is somehow considered in the above VLOOKUP): =IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, " ",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)) Where $A$1 = NOW any help gratefully acknowleged and appreciated ! A few suggestions. 1. To calculate age, use the simpler (but mostly undocumented in Excel) formula: =IF(OR(E3="",E3TODAY()),"",DATEDIF(E3,TODAY(),"y" )) or, with A1: =TODAY() =IF(OR(E3="",E3A1),"",DATEDIF(E3,A1,"y")) 2. The syntax of your VLOOKUP is incorrect. The third argument is supposed to be a column number within your table. You have: =(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates! $B$2:$B$74))*M3 But perhaps: VLOOKUP(F3,VLOOKUPRates!$B$2:$E$74,4)*M3 --ron |
All times are GMT +1. The time now is 08:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com