Compare 2 columns with words to 1 and to get the 3rd word.

[Postman]

Hi All

I have question for you. I have letter "h" (from high) in column A1 and
letter "L" (from low) in column B1. In column C1 i would like to get the
avarage from them which is "m" (from Medium). How to do it?

Thanks in advance

 

[Pete_UK]

Put this formula in C2:

=AVERAGE(A2,B2)

and copy down as required.

Hope this helps.

Pete

 

[Jacob Skaria]

Try

=CHOOSE(ROUND(AVERAGE(VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0),
VLOOKUP(B1,{"high",3;"low",1;"medium",2},2,0)),0),"low","medium","high")

 

[Postman]

it gives me in C2 - #DIV/0!

Put this formula in C2:

=AVERAGE(A2,B2)

and copy down as required.

Hope this helps.

Pete



.

 

[Postman]

it's not working. Any other idea?

Try

=CHOOSE(ROUND(AVERAGE(VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0),
VLOOKUP(B1,{"high",3;"low",1;"medium",2},2,0)),0),"low","medium","high")

 

[Jacob Skaria]

In cell A1 and B1 put one of the 3 status (high, low, medium) and then try
the formula in cell C1. When you say 'not working' what is the formula result.

 

[Postman]

In A1 i wrote - High
In B1 i wrote - Low
In C1 i paste the formula and give me error in "(VLOOKUP(A1,{" in "A1,"

 

[Jacob Skaria]

and what happens when you try the below formula with cell A1 with "low",
"medium etc;

=VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0)

 

[Postman]

nothing happens - gives me a error. i paste the whole formula -
=CHOOSE(ROUND(AVERAGE(VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0),VLOOKUP(B1,{"high",3;"low",1;"medium",2},2,0)),0),"low","medium","high")

 

[Jacob Skaria]

In Sheet2 enter the below info in ColA/B

ColA ColB
high 3
medium 2
low 1

and use the below formula in a different sheet

=INDEX(Sheet2!A1:A3,MATCH(ROUND(AVERAGE(
VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0),
VLOOKUP(B1,Sheet2!$A$1:$B$3,2,0)),0),Sheet2!B1:B3,0))

 

[Max]

Well, if you change all the "high", "low", "medium" in Jacob's formula to: H,
L, M
for consistency with your actual lookup values in A1 & B1 (you mentioned:
"h", "L", "m"), think you'd get it to work. Data matched must be consistent
for it to work.

 

[Postman]

I did what you said in sheet2 and in sheet 3 i just paste the formula in cell
A1 and it gives me a error in "A3,MATCH"

 

[Postman]

Let me get clear.
Sheet 1.
cell a1 - High
Cell b1 - Low
Cell C1 - looking to get formula to recieve avarage from high and low, which
is "medium". So in Cell C1 i need to have Medium.

The second this is: Cell A1 - High, Cell B1 - Medium, in C1 i need High or
Medium - it doesn't matter for me, because i can change it in formulas i
think. It depends what i'm gonna need to receive.

Nick

 

[Jacob Skaria]

In Sheet3

cell A1 enter high
cell B1 enter low
cell C1 apply the formula..

Do you have the argument separator as comma or semicolon....Try changing
that to semicolon...