if (this range) then (this value)

[tonywhite]

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

 

[PaddyD]

Hi - welcome to the board.

the function you need is vlookup. Check it out in the help files or search on this board for examples (there will be hundreds!).

A simple example:

http://mrexcel.com/board/viewtopic.php?topic=21613&forum=2

Post back with how you get on...

Paddy

 

[Juan Pablo González]

How about this other approach ?

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

 

[Mark W.]

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

 

[Juan Pablo González]

One more Mark

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

 

[colinharwood]

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

 

[Mark W.]

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

 

[Juan Pablo González]

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 !!!

 

[Vlad]

On 2002-09-10 15:44, Juan Pablo G. wrote:
How about this other approach ?

=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

 

[PaddyD]

=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.

Paddy