Help, Multiple conditional calculation

[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

[gls858]

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

[cycling-rod]

In F1 put =IF(A1=E1,"A1",IF(B1=E1,"B1",IF(C1=E1,"C1",IF(D1=E1,"D1","")))) this will compare the maximum number shown in E1, and the cell's location will appear in F1. Copy the formula down the F column. A problem is that if two cells, A1 and B1, for instance, contain the same number, only the first cell's location A1 will be shown in F1. Best I can come up with at my basic level of excel knowledge.

[SteveS]

"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.)

[wwj]

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

[SteveS]

"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.)

[gatorx]

I think this is what you want, please let me know if I'm correct:

=ADDRESS(COLUMN(),MATCH(E1,A1:A4,0),4)
=ADDRESS(COLUMN(),MATCH(E2,A5:A8,0),4)
=ADDRESS(COLUMN(),MATCH(E3,A9:A12,0),4)
=ADDRESS(COLUMN(),MATCH(E4,A13:A16,0),4)