new excel user

meta182 · Mar 27th, 2002, 07:33 AM

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 ]

LarryJ · Mar 27th, 2002, 08:32 AM

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 ]

IML · Mar 27th, 2002, 08:36 AM

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?

markbanin · Mar 27th, 2002, 08:42 AM

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

wi_guy · Mar 27th, 2002, 09:03 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 07:33 AM	#1
meta182 New Member Join Date: Mar 2002 Location: mike Posts: 4	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 ]

Mar 27th, 2002, 08:32 AM	#2
LarryJ Board Regular Join Date: Mar 2002 Location: Houston, TX Posts: 60	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 ]

Mar 27th, 2002, 08:36 AM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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?

Mar 27th, 2002, 08:42 AM	#4
markbanin New Member Join Date: Mar 2002 Posts: 14	I don't understand, under what conditions do you want to add the 3 columns, please explain more.

Mar 27th, 2002, 09:03 AM	#5
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	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+J2W2+K2X2+L2*Y2 HOPE I UNDERSTOOD IT RIGHT