Percentages with negative goals

[amigoes5]

Hi. I need to get a percentage, whether it is negative or positive or 100% of goal for column E (for ie. Chardonnay would be +168%; CS would be -61%). Everything I have tried from previous posts just brings me to a % sign in column E with no number. Thanks for your help.

[TABLE="width: 745"]
<tbody>[TR]
[TD][/TD]
[TD]Shipment Goals thru January[/TD]
[TD]%[/TD]
[TD]Actual Shipments YTD[/TD]
[TD](+/-) Percent of Goal(Col E)[/TD]
[TD]Cases Needed to Order[/TD]
[/TR]
[TR]
[TD]Chardonnay[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]168[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Res. Chardonnay[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TT[/TD]
[TD]28[/TD]
[TD]100%[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FB[/TD]
[TD]464[/TD]
[TD]100%[/TD]
[TD]504[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ME[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CS[/TD]
[TD]109[/TD]
[TD]100%[/TD]
[TD]42[/TD]
[TD][/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]Rcab[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TR[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]68[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]SI[/TD]
[TD]176[/TD]
[TD]100%[/TD]
[TD]56[/TD]
[TD][/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]BL[/TD]
[TD]112[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]WF[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bk40[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PN[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LCR[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]957[/TD]
[TD]100%[/TD]
[TD]826[/TD]
[TD][/TD]
[TD]381[/TD]
[/TR]
</tbody>[/TABLE]

 

[misrasomendra]

Hi amigoes5,

Try using, =IF(B1=0,D1/100,(D1-B1)/B1)
here B1 is goal, D1 is actual.

 

[amigoes5]

Thank you Misrasomendra! It worked. I now see what I was doing wrong. Thanks for your help. Now I just need to figure out how to make it automatically highlight red when negative.

 

[misrasomendra]

Welcome anytime.