Multifunction formula Help

[bundyanimal]

Hi Guys,

I am trying to come up with a formula to a cell that returns text from a few rules I have set up.

Refer to the attached Excel spreadsheet.

Column I - the returned value, based on a set of rules.

The Rules:

(ECV = Estimated Contract Value)


ECV < 250,000 & GAIN TYPICAL AOA
ECV < 250,000 & LOSS ALT
ECV > 250,000 < 100,000 & GAIN AMT
ECV > 250,000 < 100,000 & LOSS ALT
ECV > 1,000,000 ALT

So to take the first rule as an example: If the estimated contract value is less than 250,000 and there is a gain, then I would like to return the text "TYPICAL AOA" in Cell I6

Additionally, would like conditional formatting:

If formula returns:

TYPICAL AOA SIGNOFF - The cell colure to be YELLOW
AMT - The cell colour to be BLUE
ALT - The cell colour to be GREEN

Thanks in advance.

Jeff

[davsth]

the formula
=IF(OR(F6>$G$20,AND(F6>$G$19,H6<0)),"ALT",IF(F6>$G$19,"AMT",IF(H6<0,"ALT SIGNOFF","TYPICAL AOA SIGNOFF")))

conditional format cell value contains text and colour appopriately

[bundyanimal]

Excellent,

Thanks very much. Tested and all good. Just need to try and provide a blank cell if there is nothing there.

It seems to return "TYPICAL AOA SIGNOFF" when there are no values in the other cells.

No biggie though.

Thanks.

[davsth]

=if(f6="","",IF(OR(F6>$G$20,AND(F6>$G$19,H6<0)),"ALT",IF(F6>$G$19,"AMT",IF(H6<0,"ALT SIGNOFF","TYPICAL AOA SIGNOFF"))))

[bundyanimal]

Thanks very much

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.