Should i use IF statements?

[petethecat]

Hi All,
I have a sheet where i have a column with mix of Letters & numbers. in another column I want to assign a number to the letter (in this instance it is an "R" & i want to give this a value of 1) & i want to increase the values of the occupied cells by one. So i want to change a 1 to a two, a 2 to a three etc. To complicate maters i have a series of Zero values that i also want to assign a value to but i want to give them a value next in sequence AFTER the other numbers. So if my last number is a 5 (now updated to a six) i want the next value to be 7.

I have tried using a long IF statement but it is not really working 100%.
=IF(K19=0,0,IF(K19="R",1,IF(K19=1,2,IF(K19=2,3,IF(K19=3,4,IF(K19=4,5,IF(K19-5,6,IF(K19=6,7,IF(K19=7,8,IF(K19=8,9,IF(K19=9,10)))))))))))

This of course doesn't deal with the zeros in any useful way.

I am a bit of a novice with this kind of thing so any help would be appreciated!

 

[jtakw]

This updated formula will accommodate data With Or Without "R":

Book1
	D	E
1	2	2
2	0	7
3	4	4
4	5	5
5	1	1
6	3	3
7	0	8
8	0	9
9	0	10
10	0	11
11	6	6
Sheet620

Cell Formulas
Range	Formula
E1	=IF(D1="R",1,IF(D1=0,MAX(D$1:D$11)+COUNTIF(D$1:D1,0)+IF(COUNTIF(D$1:D$11,"R"),1,0),D1+IF(COUNTIF(D$1:D$11,"R"),1,0)))

Formula copied down.