IF Function and Scatter Plot help!!!!!

**murata789** · 02-15-2012, 12:49 PM

I am trying to convert a series of numbers into another series of numbers in excel using the IF function. I want to convert a scale of 6-0 (6,7,8,9,0) to a scale of 1-5 (1,2,3,4,5) so that 6=1, 7=2, 8=3, 9=4, 0=5 and a blank equals a blank. I have been using the if function to do this, and it works for every number except it converts both 0 and blank cells into "5". I tried putting quotation marks around the 0 to decipher between the blank and the 0, but that just converts all the 0 and blank cells into blanks. How can I adjust the formula so that 0 converts to 5 and a blank cell stays blank?

Here's the formula I've been using:
"=if(B5=6,1,if(B5=7,2,if(B5=8,3,if(B5=9,4,if(B5=0,5,if(B5="","")))))"
I've also tried:
"=if(B5=6,1,if(B5=7,2,if(B5=8,3,if(B5=9,4,if(B5=0,5,"")))))"

I'm also having some trouble with my scatter plots. Once I convert the above numbers, I want to view the correlation between these numbers and another set of ratings (1-5) on a scatter plot. There are 24 cells in each array, and the values range from 1-5 in each. For some reason, whenever there are blanks in one of the arrays, it plots 24 values on the x axis (1-24) and 6 values on the y axis (1-5). If I go in and fill in the blanks with a number, the plot fixes itself so that both x and y axes have scales from 1-5, and this stays fixed even when I remove the false numbers I had entered. Is there anyway to get the scatter plot to plot normally initially when there are blanks?

Any help would be REALLY appreciated. Thank you!!!

**JayaSrinivas** · 02-17-2012, 04:04 AM

Hi,
you can try this..
=IF(B5=6,1,IF(B5=7,2,IF(B5=8,3,IF(B5=9,4,IF(B5=0,5,IF(B5="",""))))))
It works great but when there is a blank cell in b5, then the result will be 5. Let me know is there any blank cells in the data.

Regards,
B.Jaya Srinivas.