| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 columns with words to 1 and to get the 3rd word.
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
Put this formula in C2:
=AVERAGE(A2,B2) and copy down as required. Hope this helps. Pete On Mar 9, 9:12*am, Postman wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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 "Postman" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
it gives me in C2 - #DIV/0!
"Pete_UK" wrote: Put this formula in C2: =AVERAGE(A2,B2) and copy down as required. Hope this helps. Pete On Mar 9, 9:12 am, Postman wrote: 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 . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
it's not working. Any other idea?
"Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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" wrote: 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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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) -- Jacob "Postman" wrote: 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" wrote: 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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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;"me dium",2},2,0)),0),"low","medium","high") "Jacob Skaria" wrote: 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) -- Jacob "Postman" wrote: 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" wrote: 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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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)) -- Jacob "Postman" wrote: 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;"me dium",2},2,0)),0),"low","medium","high") "Jacob Skaria" wrote: 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) -- Jacob "Postman" wrote: 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" wrote: 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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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. -- Max Singapore --- "Postman" wrote: 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;"me dium",2},2,0)),0),"low","medium","high") |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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" "Jacob Skaria" wrote: 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)) -- Jacob "Postman" wrote: 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;"me dium",2},2,0)),0),"low","medium","high") "Jacob Skaria" wrote: 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) -- Jacob "Postman" wrote: 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" wrote: 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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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 "Max" wrote: 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. -- Max Singapore --- "Postman" wrote: 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;"me dium",2},2,0)),0),"low","medium","high") |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare 2 columns with words to 1 and to get the 3rd word.
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... -- Jacob "Postman" wrote: 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" "Jacob Skaria" wrote: 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)) -- Jacob "Postman" wrote: 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;"me dium",2},2,0)),0),"low","medium","high") "Jacob Skaria" wrote: 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) -- Jacob "Postman" wrote: 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" wrote: 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. -- Jacob "Postman" wrote: it's not working. Any other idea? "Jacob Skaria" wrote: 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 "Postman" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| compare words | Excel Discussion (Misc queries) | |||
| Compare two files and update data from another file base on words ina cell separated by commas | Excel Worksheet Functions | |||
| to add a word between 2 words in all cells | Excel Discussion (Misc queries) | |||
| Compare the words in columns of the excel sheet | Excel Discussion (Misc queries) | |||
| Word Formating & Words Adding | Excel Discussion (Misc queries) | |||
Linear Mode
