Computer Hope

Software => Computer software => Topic started by: chrisle on September 19, 2011, 08:39:25 AM

Title: Excel Help!!!!!!! IF & MAX statements
Post by: chrisle on September 19, 2011, 08:39:25 AM

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

Title: Re: Excel Help!!!!!!! IF & MAX statements
Post by: Rob Pomeroy on September 21, 2011, 02:41:20 AM

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

Title: Re: Excel Help!!!!!!! IF & MAX statements
Post by: chrisle on September 22, 2011, 04:42:41 AM

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]

Title: Re: Excel Help!!!!!!! IF & MAX statements
Post by: chrisle on September 22, 2011, 07:20:31 AM

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?