Nested IF statements based on values in Named Range

[Mark Tooke]

Hi,

I have a named range O8:O27 called ContractLifeValue and I have some nested IF statements in cell K38, {=IF(ContractLifeValue<50000,"All signatories listed can sign",IF(ContractLifeValue<250000,"IH, NP, LE and TJ can sign",IF(ContractLifeValue>250000,"IH and TJ can sign")))}

My objective is, if all values in the range O8 to O27 are below 50000 then text "All signatories listed can sign" appears in cell K38, if any of the values in the range O8 to O27 are between 50000 and 250000 then text "IH, NP, LE and TJ can sign" appears in cell K38, and if any of the values in the range O8 to O27 are over 250000 then text "IH and TJ can sign" appears in cell K38.

My array formula only seems to work on values entered in to O8 and does not take into account values entered in subsequent cells in the range. Any help would be gratefully received.

Mark

 

[Scott T]

Try

=IF(MAX(contractlifevalue)<50000,"All signatories listed can sign",IF(MAX(contractlifevalue)<=250000,"IH, NP, LE and TJ can sign",IF(MAX(contractlifevalue)>250000,"IH and TJ can sign")))

 

[Mark Tooke]

Thanks Scott, that works beautifully.

Mark