Excel Help!!!!!!! IF & MAX statements

[chrisle]

Is it possible to summarise this formula 

=MAX(IF(E13>0,E3,0),IF(F13>0,F3,0),IF(G13>0,G3,0))
 

Basically the range is going to run from column E to AB, looking for values in row 13, return the value in row 3 & then from those answers display the highest value. I'm using excel 2003 & its limited to 30 arguments (per function) which means so far i've had to do it over 3 cells & then in another cell return the max of those 3 cells, very frustrating

Thanks

[Rob Pomeroy]

I wonder if the LOOKUP function might better suit your needs?  http://www.techonthenet.com/excel/formulas/lookup.php

[chrisle]

Sorry how would that work? I've attached a excel file briefly showing what I'm trying to do

The formula in cell N4 (i.e the answer) is below =MAX(IF(B4>0,$B$3,0),IF(C4>0,$C$3,0),IF(D4>0,$D$3,0),IF(E4>0,$E$3,0),IF(F4>0,$F$3,0),IF(G4>0,$G$3,0),IF(H4>0,$H$3,0),IF(I4>0,$I$3,0),IF(J4>0,$J$3,0),IF(K4>0,$K$3,0))

Does that make sense (sorry never done this before)? basically i want it to look along row 4, if its got a number in return the value directly above in row 3 & then give me the highest value

Thanks

[regaining space - attachment deleted by admin]

[chrisle]

This seems to work but i have to adjust the offset rows for each line

=OFFSET(INDEX(E7:BR7,MATCH(9.99999999999999E+307,E7:BR7)),-4,0,1,1)

Is there an easy way around this?