Need Substitute for Conditional Formatting

[samsilverman]

I am using excel 2007.

I have a formula in cell J12 that produces a number from a list depending on what the value is in C8. But, I need that number to be negative (formatted red with parentheses) if cell J11=0 and L11<0.

I know I can do the RED part through conditional formatting, but how do I do the ( ) part?

 

[mikerickson]

Do you need to change the number to negative if those conditions are met?
Or do you just need to change the appearance of the number?

A postitive number can show in red (with parenthesis) by Number Formatting, but that won't change the underlying value to negative.

 

[samsilverman]

Insightful question Mike, thanks for asking. I definitely need the number to change to negative AND have that formatting.

Thanks again for any help.
http://www.mrexcel.com/forum/member.php?u=73026

 

[barry houdini]

What formula do you have in J12? Assuming the result is always positive then why not add to that formula like this

=(original formula)*IF((J11=0)*(L11<0),-1,1)

that will make the number negative when your 2 conditions apply

Now use number formatting as Mike suggests to show negative numbers as red with parentheses

 

[samsilverman]

Thanks Barry. I'd like to try the solution, but you've lost me on how to integrate it into my existing formula. Here it is below. Please don't laugh too hard if it is amateurish (it certainly is lonnnnnggggg). I am quite the newbie.

Thanks for any help.

=IF(ISBLANK(C8)," ",IF(OR($C8="AIRBORNE ISOLATION"),'FORMULAS & LISTS'!F$6,IF($C8="AUTOPSY ROOM",'FORMULAS & LISTS'!F$7,IF($C8="BRONCHOSCOPY",'FORMULAS & LISTS'!F$8,IF($C8="CLEAN HOLDING",'FORMULAS & LISTS'!F$9,IF($C8="CLEAN WORKROOM",'FORMULAS & LISTS'!F$10,IF($C8="CRITICAL CARE",'FORMULAS & LISTS'!F$11,IF($C8="DARKROOM",'FORMULAS & LISTS'!F$12,IF($C8="DECONTAMINATION",'FORMULAS & LISTS'!F$13,IF($C8="DELIVERY ROOM",'FORMULAS & LISTS'!F$14,IF($C8="ENDOSCOPY",'FORMULAS & LISTS'!F$15,IF($C8="ENDO INSTRUMENT PROCESSING",'FORMULAS & LISTS'!F$16,IF($C8="ER WAITING ROOM",'FORMULAS & LISTS'!F$17,IF($C8="ETO-STERILIZER",'FORMULAS & LISTS'!F$18,IF($C8="EXAMINATION ROOM",'FORMULAS & LISTS'!F$19,IF($C8="INTENSIVE CARE",'FORMULAS & LISTS'!F$20,IF($C8="INTERMEDIATE CARE",'FORMULAS & LISTS'!F$21,IF($C8="ISOLATION ANTEROOM - POS",'FORMULAS & LISTS'!F$22,IF($C8="ISOLATION ANTEROOM - NEG",'FORMULAS & LISTS'!F$23,IF($C8="L&D RECOVERY",'FORMULAS & LISTS'!F$24,IF($C8="LAB - BIOCHEMISTRY",'FORMULAS & LISTS'!F$25,IF($C8="LAB - CYTOLOGY",'FORMULAS & LISTS'!F$26,IF($C8="LAB - GENERAL",'FORMULAS & LISTS'!F$27,IF($C8="LAB - HISTOLOGY",'FORMULAS & LISTS'!F$28,IF($C8="LAB - MICROBIOLOGY",'FORMULAS & LISTS'!F$29,IF($C8="LAB - NUCLEAR MEDICINE",'FORMULAS & LISTS'!F$30,IF($C8="LAB - PATHOLOGY",'FORMULAS & LISTS'!F$31,IF($C8="LAB - SEROLOGY",'FORMULAS & LISTS'!F$32,IF($C8="NEWBORN INTENSIVE CARE",'FORMULAS & LISTS'!F$33,IF($C8="NONREFRIG BODY ROOM",'FORMULAS & LISTS'!F$34,IF($C8="O.R.",'FORMULAS & LISTS'!F$35,IF($C8="PATIENT ROOM",'FORMULAS & LISTS'!F$36,IF($C8="PHARMACY",'FORMULAS & LISTS'!F$37,IF($C8="PROCEDURE ROOM",'FORMULAS & LISTS'!F$38,IF($C8="PROTECTIVE ENVIRONMENT",'FORMULAS & LISTS'!F$39,IF($C8="RECOVERY",'FORMULAS & LISTS'!F$40,IF($C8="SOILED HOLDING",'FORMULAS & LISTS'!F$41,IF($C8="SOILED WORKROOM",'FORMULAS & LISTS'!F$42,IF($C8="STERILE STORAGE",'FORMULAS & LISTS'!F$43,IF($C8="STERILIZER EQUIPMENT ROOM",'FORMULAS & LISTS'!F$44,IF($C8="SUBSTERILE",'FORMULAS & LISTS'!F$45,IF($C8="SURGICAL CYSTOSCOPIC",'FORMULAS & LISTS'!F$46,IF($C8="TOILET ROOM",'FORMULAS & LISTS'!F$47,IF($C8="TRAUMA",'FORMULAS & LISTS'!F$48,IF($C8="TREATMENT ROOM",'FORMULAS & LISTS'!F$49,IF($C8="TRIAGE",'FORMULAS & LISTS'!F$50,IF($C8="X-RAY - CATHERIZATION",'FORMULAS & LISTS'!F$51,IF($C8="X-RAY - CRITICAL CARE",'FORMULAS & LISTS'!F$52,IF($C8="X-RAY - DIAGNOSTIC",'FORMULAS & LISTS'!F$53,IF($C8="X-RAY - SURGICAL",'FORMULAS & LISTS'!F$54,IF($C8="X-RAY - TREATMENT",'FORMULAS & LISTS'!F$55)))))))))))))))))))))))))))))))))))))))))))))))))))

 

[mikerickson]

Have you considered using a VLOOKUP range?
It would be easier to maintain and edit than that long formula.

 

[samsilverman]

Haven't learned that yet. But, always interested if you can point me in the right direction.

 

[samsilverman]

You know...the best times in life are when you look really stupid (if you can laugh at yourself). Who knew it was exactly as you posted it. DOH!

Worked like a charm, Barry. You're the man!

What formula do you have in J12? Assuming the result is always positive then why not add to that formula like this

=(original formula)*IF((J11=0)*(L11<0),-1,1)

that will make the number negative when your 2 conditions apply