new excel user
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: new excel user

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    mike
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am very new to excel. I am trying to add 4 columns and 3 others only if there is a maximun value in those last 3 columns. This is what I came up with but it does not work. Can anyone help?
    =N2+P2+R2+T2+IF(MAX(J2:J25),+J29,+IF(MAX(K2:K25),+K29,+IF(MAX(L2:L25),+L29)))

    I should have been more clear, sorry I'm new and thank you for all your responses.

    The 3 columns I'm speaking of. All I want to do is look in each column, find the highest value in, let's say J2:j25 then that person associated with the high value gets another result added to his winnings. It's a golf thing. Thanks for your help sorry if this is unclear.

    [ This Message was edited by: meta182 on 2002-03-27 06:34 ]

    [ This Message was edited by: meta182 on 2002-03-27 06:37 ]

    [ This Message was edited by: meta182 on 2002-03-27 10:48 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not real clear on what you mean with the maximum value statement, but I think your problem is the Max function.

    It will return the Maximum value in the specified range. So maybe you need to compare that returned value to what you consider to be a maximum value?

    If you don't specifiy anything to compare, then Max returns the highest number in the range and the IF would consider that to be a TRUE value, so you would always get J29 added to your value (unless there was no data at all in the range).

    If you are wanting to add J29, K29 and L29 to the other 4, then I think your IF statement is going to have to be MUCH bigger.

    As it is, you can only do one thing in the TRUE/FALSE part of the IF statement. So you could not have +J29 and include more processing for K29 and L29 in the TRUE part of the first IF statement.

    Maybe something like this would work (assuming there is a known MAX value of 99):

    =N2+P2+R2+T2+IF(MAX(J2:J25)=99,+J29,0)+IF(MAX(K2:K25)=99,+K29,0)+IF(MAX(L2:L25)=99,+L29,0)

    This should add J29 to the first 4 if the max value in the range is 99;otherwise it adds 0 (not really needed but good form). Then goes on to add each of the other values under the same conditions.

    Hope that helps.

    [ This Message was edited by: LarryJ on 2002-03-27 07:33 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean by only if there is a maximun value in those last 3 columns?

    Every column with numbers would have a maximum value, wouldn't it? It looks like you may be on the right track, but you just need to compare your if statement do something. If the maximum of that column is X, what should you compare it to to see if it should be added?

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't understand, under what conditions do you want to add the 3 columns, please explain more.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    If you can add a column that checks for the max in columns J,K,L, etc.....like....=IF(J2=MAX(J$2:J$25),1,0).....you would get a 1 for the MAX # and 0 for the rest.....then your summation formula would be (assuming the new columns are WXY) N2+p2+r2+t2+J2*W2+K2*X2+L2*Y2

    HOPE I UNDERSTOOD IT RIGHT

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com