Ok Excel guru's how about this function

  • Thread starter Thread starter Jeffrey Sheldon
  • Start date Start date
J

Jeffrey Sheldon

I need to have Excel review the last digit of a value in
field A and then based on that digit enter a value in B.

If field A has a value of 76541 whereas the 5th digit =
1, then field B needs to auotmatically be populated
with '00' (no quotes)

If field A has a value of 76542, whereas the 5th digit =
2, then field B needs to automatically be populated
with '02'

I have tried LEFT, SUM and Other formulas but just can't
get it to work. Is this possible?
 
=IF(RIGHT(A1)="1",0,IF(RIGHT(A1)="2",2,""))

format as 00

or if you want text value

=IF(RIGHT(A1)="1","00",IF(RIGHT(A1)="2","02",""))

if the number of digits can be more than 5 but you always want the fifth

=IF(MID(A1,5,1)="1",0,IF(MID(A1,5,1)="2",2,""))

for text

=IF(MID(A1,5,1)="1","00",IF(MID(A1,5,1)="2","02",""))

the 2 last formulas work if the number has 5 or more digits
so the first 2 can be disregarded
 
Jeffrey,
The problem with using the if statement is if you have a
lot of values to compare your if to, the formula just gets
too messy. In your example, it's easy, bec you only have
2 possible returns, which is "00" if a 1 or "02" if 2. If
you have more than that or if you even add to that in the
future, you have to append your formula as necessary. So,
my suggestion is nesting a right function on a vlookup
function? The simplest I can think of is to create a
table array for the vlookup. Example:

Col A Col B
1 00
2 02

So now, you can use the right and vlookup functions.
Let's say your "76541" cell is in A1, so your formula
should be "=vlookup((right(a1)*1),ColA:ColB,2,false)".
Atleast by doing this, you keep your formula short and
simple. If you have more values to add, you just do it on
your table array. This should work!
 
c said:
Jeffrey,
The problem with using the if statement is if you have a
lot of values to compare your if to, the formula just gets
too messy. In your example, it's easy, bec you only have
2 possible returns, which is "00" if a 1 or "02" if 2. If
you have more than that or if you even add to that in the
future, you have to append your formula as necessary. So,
my suggestion is nesting a right function on a vlookup
function? The simplest I can think of is to create a
table array for the vlookup. Example:
Click to expand...

If the values in A really are numbers or numbers formatted as text, what's
wrong with B1=(mod(A1,10)-1)*2, with column B custom formatted as 00? It
seems to work for me but perhaps I have misread the question.

Jim.
 
I need to have Excel review the last digit of a value in
field A and then based on that digit enter a value in B.

If field A has a value of 76541 whereas the 5th digit =
1, then field B needs to auotmatically be populated
with '00' (no quotes)

If field A has a value of 76542, whereas the 5th digit =
2, then field B needs to automatically be populated
with '02'

I have tried LEFT, SUM and Other formulas but just can't
get it to work. Is this possible?
Click to expand...

Why would you use LEFT? Why SUM?

The rightmost character of cell X evaluating as text is given by RIGHT(X,1),
though it's often safer to use RIGHT(TRIM(X),1). The rightmost digit of cell X
evaluating as numeric formatted as integer is given by MOD(ROUND(X,0),10).

Mapping one set of text to another is best done using a lookup function. I
prefer LOOKUP rather than VLOOKUP for this.

=LOOKUP(--RIGHT(TRIM(X),1),{0;1;2;3;4;5;6;7;8;9},
{"-";"00";"02";"-";"-";"-";"-";"-";"-";"-"})
 
James Silverton said:
If the values in A really are numbers or numbers formatted as text, what's
wrong with B1=(mod(A1,10)-1)*2, with column B custom formatted as 00? It
seems to work for me but perhaps I have misread the question.
Click to expand...
Or even simpler, B1=mod(A1-1,2)*2, if the only possibilities are numbers
ending with 1 or 2 in A.

Jim
 
Back
Top