using IF function

  • Thread starter Thread starter H_K
  • Start date Start date
H

H_K

Im new to this but here is my problem.

One of my columns is age. I want to input either A, B, C, D, E, F i
the cell and have it return either: under -17, 18-24, 25-30, 31-39
40-55, and 56-over respectively. I was trying to use the IF functio
but not sure if that is the best way to do it. Here is something lik
what i had.

IF(B2:B100=CHAR(A),"Under 17", IF(B2:100=CHAR(B), "18-24".......

Can you apply a function to effect the whole column or would have t
just put

IF(B2=CHAR(A), ... and do that for every cell in B. I also have
question about referencing a character and not the cell. Is using th
CHAR function the proper way and if so do i have to input the ansi # o
something else. Im kinda lost and I know this is alot.

I am probably way over my head and I don't even know if something lik
this can be done.
thanks for any hel
 
Hi
try the following:
1. Setup a lookup table (e.g. called 'lookup') on a separate sheet with
the following layour
A B
1 A under 17
2 B 18-24
3 ...

Now on a different sheet enter in cell A1 your character and in B1 use
the formula
=VLOOKUP(A1,'lookup'!$A$1:$B$10,2,0)
 
Instead of an IF() function, I would use a Lookup:

Put your letters/ages in an out-of-the way portion of your sheet, or on
a second sheet (say, Sheet2!A1:B6):

A B
1 A Under 17
2 B 18-24
....
6 F 56-over

Then back in Sheet1, enter

=VLOOKUP(B2, Sheet2!A:B, 2, TRUE)
 
Thank you very much for the replies. Like I said this is all kinda ne
to me but im going to see what i can do with the replies you hav
given. I was wanting to be able to input "A" in the cell and hit ta
to go the next and it change to "under 17". Dont know if that i
possible. Thanks again thoug
 
Hi
if you want the cell in which you have entered 'A' to change it would
require VBA (using an event procedure). Both JE's and my solution
asusmed a second cell with this formula in it.

As an alternative you may consider using a drop-down for this. See:
http://www.contextures.com/xlDataVal01.html
 
I was wanting to be able to input "A" in the cell and hit tab
to go the next and it change to "under 17". Dont know if that is
possible.
Click to expand...

In order to do this, you would need to use VBA, and create a
worksheet_onchange function that looks to see if you are in one of these
special cells and if you just entered A, B, C, D, E, and then changes it to
the appropriate text.

Have you looked at Data/Validation at all?
 
Back
Top