MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Error message help


Posted by Rich on October 01, 2001 4:10 PM

I have developed a questionnaire using excel.
As the interviewee answers a question, the following equation returns an corrsponding number (1-5);
=VLOOKUP(Scale,Score,2,FALSE)

My problem is that if the question is not answered or left clear(which will happen a lot), the vlookup returns #N/A.

So a series of data returned from this function might be 2,3,5,1,#N/A,3,#N/A. I want to average and get a max/min from this data treating the #N/A as a 0.

Thanks,

Rich



Posted by Richard S on October 01, 2001 6:19 PM

Try =IF(ISNA(VLOOKUP(Scale,Score,2,FALSE)),"",VLOOKUP(Scale,Score,2,FALSE))
Richard


Posted by Rich on October 02, 2001 4:05 AM

Thanks.

It works.

Rich