IF Statement off of a max of 4 values

[cliff_davis]

I have 4 summed values in seperate columns and I want to run an if statement based on the max value of the 4 columns and if the IF statement is not true then run a formula to get the weighted average of the remaining the 3 values.

Ex. 66, 75, 41, 39 ( there are columns in between the values as well)

I want the cell below 75 to denote the full percent value (in my case 35%), for the cells that are less than 75 I will have the formula to get the weighted average ( I have this done)

The trouble I am running into is the logic function of the IF statment, How can I get it to read the Max number of the seperate cells to run the true logic or the false logic? Please no VBA.

Thank you in advance for the help.

 

[lenze]

Hi and welcome to the Board!!!
What is your formula for the "Weighted Average"?
lenze

 

[cliff_davis]

Thanks for the reply...below is the formula I have been using for files with only 2 comparison samples, having trouble bumping up to 3 and 4.

=IF(I27>E27,0.35,((I27*0.35)/(E27*0.35)*0.35))

I just want to use this same forumula, but in comparing 3 and 4 sets of summed data, but am having trouble getting the front end of the IF statment to pull the max and then calculate the remaining formulas.

 

[lenze]

=SUM(A1,C1,E1,G1)-MAX(A1,C1,E1,G1)

lenze