Change military grades into rank

[air force guy]

Afternoon all
Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work consistently, sometimes blank cell is civilian and formula stops working

 

[air force guy]

Excel Formulas.xlsx
	C	D	E	F	G
1	GRADE	RANK		Exer/Real	Plan
2	1	2 LT			01A1A
3	2	1 LT			01B1B
4	3	Capt			01C1C
5	4	Maj			01D1D
6	5	Lt Col			01E1E
7	6	Col			01F1F
8	7	Brig Gen
9	8	Maj Gen
10	9	Lt Gen
11	10	Gen
12		CIV
13	31	E1
14	32	E2
15	33	E3
16	34	E4
17	35	E5
18	36	E6
19	37	E7
20	38	E8
21	39	E9
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	=IF(C2=1,"2 LT",IF(C2=2,"1 LT",IF(C2=3,"Capt",IF(C2=4,"Maj",IF(C2=5,"Lt Col",IF(C2=6,"Col",IF(C2=7,"Brig Gen",IF(C2=8,"Maj Gen",IF(C2=9,"Lt Gen",IF(C2=10,"Gen",IF(C2=31,"E1",IF(C2=32,"E2",IF(C2=33,"E3",IF(C2=34,"E4",IF(C2=35,"E5",IF(C2=36,"E6",IF(C2=37,"E7",IF(C2=38,"E8",IF(C2=39,"E9",IF(C2="","CIV"))))))))))))))))))))

Going to use same IF formula for "Exer/Real" column that I used in Grade/Rank columns

 

[air force guy]

All you do is select your range, click that tab, click capture range, click generate output (you'll probably get a message box to ok) and paste in the reply window.

I'll look at it tomorrow if no-one else does as getting close to 1am here.

Holy smokes...thanks again

 

[MARK858]

As I said I'll look at it tomorrow if no-one else does as getting close to 1am here but in the meantime can you explain what you want to appear in Exer/Real as I am still not 100% clear, I assume what is in the Plan column?

sometimes blank cell is civilian and formula stops workin

This might be an issue. How is Excel going to tell the difference between a cell that is blank that reference civilian and a blank cell that doesn't?

 

[air force guy]

As I said I'll look at it tomorrow if no-one else does as getting close to 1am here but in the meantime can you explain what you want to appear in Exer/Real as I am still not 100% clear, I assume what is in the Plan column?


This might be an issue. How is Excel going to tell the difference between a cell that is blank that reference civilian and a blank cell that doesn't?

the blank cell is no longer the issue (when you mention spaces in the cell I went back and took out all the extra space and it works as it should)
the Exercise/Real column references the plan column. It looks at that plan and (via the IF formula) I will tell it what is real and what is exercise (I have a table to reference)

 

[air force guy]

the blank cell is no longer the issue (when you mention spaces in the cell I went back and took out all the extra space and it works as it should)
the Exercise/Real column references the plan column. It looks at that plan and (via the IF formula) I will tell it what is real and what is exercise (I have a table to reference)

My question is...can I do it quicker without writing that long IF formula?