Complicated formula

A

A. Stern

I have an Excel sheet with probably 1200 cells. Each cell may contain a
character which represents a numerical value (ex. P = .75, R - .50, etc.).
Certain letters represent a higher value, and I want the higher of the two
values. How can I compare every cell and arrive at a number?


Thnaks
 
D

Don Guillett

try
=IF(CODE(C10)>CODE(C11),C10,C11)
or
=IF(upper(CODE(C10))>upper(CODE(C11)),C10,C11)
 
E

Earl Kiosterud

A.,

Make a table of your values. They don't have to be in order. This one is in E2:F3

P .75
R .5

I don't know what you mean by "compare every cell." Every cell of what? If you want to get
the relative values of the P's and R's, sort the table. Now the first column will give you
them in ascending order.

You can compare two cells, A2 and A3, like this:

=IF(VLOOKUP(A2, E2:F3, 2, FALSE) - VLOOKUP(A3, E2:F3, 2, FALSE), "A2 is larger", "A3 is
larger")
 
D

Don Guillett

This should find the largest in col C.
This is an ARRAY formula which must be entered using ctrl+shift+enter
="c"&
MATCH(CHAR(MAX(IF(ISNUMBER(CODE($C$1:$C$31)),CODE($C$1:$C$31),""))),C:C,0)
 
A

A. Stern

Earl Kiosterud said:
A.,

Make a table of your values. They don't have to be in order. This one is
in E2:F3

P .75
R .5

I don't know what you mean by "compare every cell." Every cell of what?
If you want to get the relative values of the P's and R's, sort the table.
Now the first column will give you them in ascending order.

You can compare two cells, A2 and A3, like this:

=IF(VLOOKUP(A2, E2:F3, 2, FALSE) - VLOOKUP(A3, E2:F3, 2, FALSE), "A2 is
larger", "A3 is larger")
Click to expand...

I need to compare every cell with each other to determine a correlation.
 
R

Ron Coderre

Are you saying that one cell may contain 2 letters? (eg A1: PR)
Can you post some sample cell contents and what you hope to calculate from
them?

***********
Regards,
Ron

XL2002, WinXP
 
Top