Help, Multiple conditional calculation

  • Thread starter Thread starter wwj
  • Start date Start date
W

wwj

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
 
wwj said:
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
Click to expand...
One way I'm sure there are others. Put this in column F

=IF((MAX(A1:D1))>0,(MAX(A1:D1)),0)

gls858
 
wwj said:
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
Click to expand...
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
 
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
 
wwj said:
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
Click to expand...
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!!
 
Back
Top