How do I Define A specific a value?

  • Thread starter Thread starter dkenebre
  • Start date Start date
D

dkenebre

How do I define 3 identical values in a cell or individual cells as 3
and 2 of 3 identitical values as value 2 and 0 of 3 values equal to
1 like the following examples:


777 = 3
277 = 2
247 = 1

and so on..........
 
If I understand you correctly, I believe a VLOOKUP formula will do what you
want.......
Create a RangeName of your values down one column and the digets you would
like to represent them in the next column to the right.......name this table
"MyTable"

then, assuming the value you wish to convert is in cell A1, put this formula
in B1.....

=vlookup(A1,MyTable,2,false)

hth
Vaya con Dios,
Chuck, CABGx3
 
Let me add to my explanation of what I would like to do:

Suppose a value A1 = 7 and B1 = 7 and C1 = 7, I want D1 to
automatically enter a value of 3. So that whenever a value has all 3
cells the same then the following cell's value is 3.
Also
Suppose a value A1 = 2 and B1 = 7 and C1 = 7, I want D1 to
automatically enter a value of 2. So that whenever a value has all
2cells alike then the following cell's value is 2.
Also
Suppose a value A1 = 2 and B1 = 4 and C1 = 7, I want D1 to
automatically enter a value of 1. So that whenever a value has all none
of the 3 cells alike then the following cell's value is 1.
 
=SUM(IF(A1=E1,1,0),IF(B1=E1,1,0),IF(C1=E1,1,0))

Put your 7 in cell E1, or hard code it in the formula to replace E1 if you
wish......

Vaya con Dios,
Chuck, CABGx3
 
Hi dkenebre,

Try this modified version of Debra's formula:

=IF(AND(A1=B1,A1=C1),3,IF(AND(A1<>B1,A1<>C1,B1<>C1),1,2))

Biff
 
Biff said:
Hi dkenebre,

Try this modified version of Debra's formula:

=IF(AND(A1=B1,A1=C1),3,IF(AND(A1<>B1,A1<>C1,B1<>C1),1,2)) ....
Click to expand...

As an alternative,

=COUNTA(1,A1:C1)-SUMPRODUCT(1/COUNTIF(A1:C1,A1:C1))

which scales a lot better if there were, say, 100 cells with values to
compare.
 
Back
Top