In Excel, I'm attempting to set up multiple formulas within one cell. Am I trying to do the impossible?

Question

First of all, thank you to the person who answered my previous Excel question! It was exactly what I needed.

One section of my spreadsheet is % increase/decrease in sales. The equation is:

+(J12-D12)/D12

J12 is sales for June '07
D12 is sales for June '06

#1 When there is a decrease, I change the negative figure to red. I've figured out how to do that, but not in addition to the equation.

#2 Sometimes, last year's sales were zero and the increase/decrease shows up as the error "DIV/0!". I change those to "n/a"...So I'd like to be able to make that automatic also.

So, I have the original equation. I want to have the font automatically change to red when negative. And I want "DIV/0!" to automatically show up as "n/a"....Is this possible?!

I'm getting giddy. It's working. But one more question before I'm done.

How do I string more than one formula together? Like, I need to save the formula from Jan to Dec....but right now, July to Dec needs to be blank. Instead it's showing up as n/a all the way down. I've tried adding an ISBLANK formula to the n/a one, but it's not taking...

Answer 1

#1 showing the negative % as red:
- Format -> Cells -> Number tab
- Pick Custom in the Category window
- In the Type window, put this: 0%;[Red]-0%;0%

#2 checking for 0 sales:
- IF (D12>0, (J12-D12)/D12, "n/a")
or, my somewhat prefered, shorter version:
- IF (D12>0, J12/D12-1, "n/a")

Good luck.
.

Answer 2

Use an IF statement to make the DIV/O error to say N/A. You basically want excel to see if D12 is zero first, if so then display N/A, if not then calculate the formula. Therefore...

=IF(D12<>0, (J12-D12)/D12,"N/A")

Then just use conditional formatting to display the result in red when negative.