LOOKUP I think!
 

Hi,

This relates to an earlier thread I posted about more than 7 nested IF
statements - Harlan Grove gave me a wonderful formula which doesn't work
exactly how I need but has led me down another line of thinking to use lookup
instead of IFs.

I need to display the margin for lowest selling price in column BE. I've
potentially got selling prices for 4 volume breaks (columns X, AG, AP, AY) -
if these are changing users will update selling prices in seperate columns
(columns AA, AJ, AS, BB) - there may be a new price entered where it was
previously 0.

So basically my question is... can I use a formula to simply list the most
right-hand value out of all these cells (I was previously checking if certain
cells were < to 0 and recalculating)???

Thanks, Mel

LOOKUP I think!
 

try something like
=if(bb<0,bb,if(as<0,as,if(aj<0,aj,aa)))

"Meltad" wrote:

Hi,

This relates to an earlier thread I posted about more than 7 nested IF
statements - Harlan Grove gave me a wonderful formula which doesn't work
exactly how I need but has led me down another line of thinking to use lookup
instead of IFs.

I need to display the margin for lowest selling price in column BE. I've
potentially got selling prices for 4 volume breaks (columns X, AG, AP, AY) -
if these are changing users will update selling prices in seperate columns
(columns AA, AJ, AS, BB) - there may be a new price entered where it was
previously 0.

So basically my question is... can I use a formula to simply list the most
right-hand value out of all these cells (I was previously checking if certain
cells were < to 0 and recalculating)???

Thanks, Mel

LOOKUP I think!
 

Thanks BJ, I thought I'd be pushing the 7 IF limit on this but forgot the
last cell would be the 'false' value.
Cheers

"bj" wrote:

try something like
=if(bb<0,bb,if(as<0,as,if(aj<0,aj,aa)))

"Meltad" wrote:

Hi,

This relates to an earlier thread I posted about more than 7 nested IF
statements - Harlan Grove gave me a wonderful formula which doesn't work
exactly how I need but has led me down another line of thinking to use lookup
instead of IFs.

I need to display the margin for lowest selling price in column BE. I've
potentially got selling prices for 4 volume breaks (columns X, AG, AP, AY) -
if these are changing users will update selling prices in seperate columns
(columns AA, AJ, AS, BB) - there may be a new price entered where it was
previously 0.

So basically my question is... can I use a formula to simply list the most
right-hand value out of all these cells (I was previously checking if certain
cells were < to 0 and recalculating)???

Thanks, Mel