Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
wwj
 
Posts: n/a
Default 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   Report Post  
gls858
 
Posts: n/a
Default

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   Report Post  
SteveS
 
Posts: n/a
Default

"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   Report Post  
wwj
 
Posts: n/a
Default

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   Report Post  
SteveS
 
Posts: n/a
Default

"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create Multiple Conditional Formulas in a single cell? Maxfx Excel Discussion (Misc queries) 2 February 1st 05 03:26 PM
how do i use multiple conditional formats in one cell? tysonstone Excel Discussion (Misc queries) 1 January 22nd 05 12:15 AM
Copy conditional formatting across multiple rows? Gil Excel Discussion (Misc queries) 1 January 11th 05 12:27 PM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM
How do I use conditional formatting for multiple rows? Jim Johnson Excel Worksheet Functions 1 October 30th 04 03:36 AM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"