Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help, Multiple conditional calculation
I have a question about multiple conditional calculation. Here is my
question. A B C D E F A1 B1 C1 D1 =Max(A1:D1) A2 B2 C2 D2 =Max(A2:D2) A3 B3 C3 D3 ... A4 B4 C4 D4 ... I have a database like row 1-4 and column A to D. (The actual database is much bigger.) I konw how to get the maximum value as I write above. My question is how to get the location of the maximum number in F colum? For example, if in E3, the maximum number is B3, then F should be B3. I have checked with the excel help, but just find a "if function" with two conditions. So what can I do? I am a greenhand in advanced Excel and know nothing about VBA program. So if the program is needed, would you please explain in a little detail? Thanks. wwj |
#2
|
|||
|
|||
wwj wrote:
I have a question about multiple conditional calculation. Here is my question. A B C D E F A1 B1 C1 D1 =Max(A1:D1) A2 B2 C2 D2 =Max(A2:D2) A3 B3 C3 D3 ... A4 B4 C4 D4 ... I have a database like row 1-4 and column A to D. (The actual database is much bigger.) I konw how to get the maximum value as I write above. My question is how to get the location of the maximum number in F colum? For example, if in E3, the maximum number is B3, then F should be B3. I have checked with the excel help, but just find a "if function" with two conditions. So what can I do? I am a greenhand in advanced Excel and know nothing about VBA program. So if the program is needed, would you please explain in a little detail? Thanks. wwj One way I'm sure there are others. Put this in column F =IF((MAX(A1:D1))0,(MAX(A1:D1)),0) gls858 |
#3
|
|||
|
|||
"wwj" wrote:
I have a question about multiple conditional calculation. Here is my question. A B C D E F A1 B1 C1 D1 =Max(A1:D1) A2 B2 C2 D2 =Max(A2:D2) A3 B3 C3 D3 ... A4 B4 C4 D4 ... I have a database like row 1-4 and column A to D. (The actual database is much bigger.) I konw how to get the maximum value as I write above. My question is how to get the location of the maximum number in F colum? For example, if in E3, the maximum number is B3, then F should be B3. I have checked with the excel help, but just find a "if function" with two conditions. So what can I do? I am a greenhand in advanced Excel and know nothing about VBA program. So if the program is needed, would you please explain in a little detail? Thanks. wwj If I understand right, you want to know the cell address that holds the max value in each row? If this is right, it only takes two functions: Address() and Match() The formula in Column F is: =ADDRESS(ROW(),MATCH(E1,A1:D1,0),4) Notes: the Match function needs the third parameter (0) if you want an absolute cell referance ($A$1), delete the last comma and the 4 HTH --- SteveS -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
#4
|
|||
|
|||
Thanks, SteveS.
I got it! One more question. If the data is not listed in column "A", "B", "C" and "D". However they are listed in the same column, but in defferent sections, as A1:A4, A5:A8, A9:A12 and A13:A16 instead. If I want you get the maximum of (A1, A5, A9, A13) in E1, and get the address of the maximum number in F1. How can I do that? Seems that the following function will not work. =3DADDRESS(ROW(),MATCH(E1,{"A1","A5","A9","A13"},= AD0),1)=20 WWJ |
#5
|
|||
|
|||
"wwj" wrote:
Thanks, SteveS. I got it! One more question. If the data is not listed in column "A", "B", "C" and "D". However they are listed in the same column, but in defferent sections, as A1:A4, A5:A8, A9:A12 and A13:A16 instead. If I want you get the maximum of (A1, A5, A9, A13) in E1, and get the address of the maximum number in F1. How can I do that? Seems that the following function will not work. =ADDRESS(ROW(),MATCH(E1,{"A1","A5","A9","A13"},Â*0 ),1) WWJ It looks like I gave you your homework answer the last time. Since I have already beenthere and done that, this time I will give you a push in the right direction. You know about the Max() function. Next, study the Match() function and its arguments. And you already know about the Address() function. Read it again. See what arguments it needs. If you use all three of the functions, you can generate the cell address of the max value of a column of numbers. Good luck!! --- SteveS -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create Multiple Conditional Formulas in a single cell? | Excel Discussion (Misc queries) | |||
how do i use multiple conditional formats in one cell? | Excel Discussion (Misc queries) | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
How do I use conditional formatting for multiple rows? | Excel Worksheet Functions |