# if (this range) then (this value)

#### tonywhite

##### New Member
Hi everyone.

I'm kind of new to excel and the coding involved, but I'd really like to take advantage of some of the powerful features to help me track things in our department. Here is a specific thing I'd like to accomplish:

I'd like for one column (column A) to be populated with a letter of the alphabet, depending on what the number is in the next column (column B). AND that dependency is based on a range of values. Example:
If column B is 1-5, then column A is "A"
If column B is 6-10, then column A is "B"
etc. all the way to "Z".

I need to allow fractions in column B as well.

Any suggestions on ways to accomplish this? Any feedback is welcomed. I appreciate a community like this.

tony

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

=CHAR(TRUNC(B1/5)-(MOD(B1,5)=0)+65)

=CHAR(INT(CEILING(A1,5)/5)+64)

One more Mark

=CHAR(CEILING(A1,5)/5+64)

Hi Tony

Try using the VLOOKUP function

In A1 put the following

=VLOOKUP(B1,\$E\$1:\$F\$100,2,0)

Where col E has the numbers to check for & col f has the corresponding letters you wish to use

Then copy the formula in A1 down col A

Cheers

Colin

On 2002-09-10 16:00, Juan Pablo G. wrote:
One more Mark

=CHAR(CEILING(A1,5)/5+64)

Ooooh, you're right... thought I needed that INT()!

You must admit that this use of CEILING() is pretty cool! Don't get to use it much, but makes for a nice step function.
This message was edited by Mark W. on 2002-09-10 16:07

On 2002-09-10 16:06, Mark W. wrote:
...You must admit that this use of CEILING() is pretty cool! Don't get to use it much, but makes for a nice step function.

It IS pretty cool, that was my first thought, but then, for some reason, I was thinking about MOD, and INT, and other useless functions !!!

On 2002-09-10 15:44, Juan Pablo G. wrote:

=CHAR(TRUNC(B1/5)-(MOD(B1,5)=0)+65)
Three questions from a rookie:
1. Trunc(b1/5) why is it 5? Is it because it's a stepping in conditions?
2. What is 65 and why?
3. How does deduction of a number from a character work? (???)
4. In (MOD(B1,5)=0... what is 5? Same thing? Stepping?

Thanks

=CHAR(TRUNC(B1/5)-(MOD(B1,5)=0)+65)

=CHAR(CEILING(A1,5)/5+64)

both these solutions rely on the char() function. As the help file says, char() "returns the character specified by a number". To see what the char() values are, put this in a1 & copy down to A255:

=char(row())

You will see that the capital letters A through Z are numbered 65 - 90. The challenge is therefore to devise a formula that increases by 1 for every increase by 5 in the data ('cos that way we can step through the letters of the alphabet every time we increase by 5)...The straightforward way to do this is divide the number in question by 5! The various trunc(), mod() & ceiling() bits are to ensure that (a) only whole numbers are returned, and (b) that the righht number is returned - to see what they are doing, separate them out from the formula & test them with various numbers.

Replies
2
Views
158
Replies
1
Views
177
Replies
7
Views
262
Replies
7
Views
142
Replies
1
Views
160

1,203,096
Messages
6,053,510
Members
444,668
Latest member
OneCat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back