trying eliminate unreasonable ve

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Trying to eliminate unreasonable +ve and -ve percentage results

# Trying to eliminate unreasonable +ve and -ve percentage results

Resolved · Medium Priority · Version 2010

### Trying to eliminate unreasonable +ve and -ve percentage results

I have a formula which calculates and returns a percentage.

I need to error proof it against displaying unreasonable values more than 4 characters and to display "N/A" instead.

e.g.

Month on month variance = +107
Current month balance = -3
percentage variance = -3083%
desired display result = N/A

This is required for both positive and negative results.
The format of my report looks like this:

Jun Jul Variance %
27,364 30,227 2,862 9%

regards

Jason

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Hi Jason

Thanks for getting in touch. You can achieve what you want through Custom Formats in Excel.

I think I've interpreted your criteria correctly but as you'll see in the example it's quite easy to change.

Highlight your percentages, right-click and choose Format Cells. On the Number tab choose Custom. Under Type enter the following:

[<=-999]"N/A";[>=999]"N/A";0.00%

This uses IF THEN ELSE logic, separated by semicolons. If the value is less than -999 replace it with N/A. If the value is greater than 999 then replace it with N/A. Otherwise format it as regular 2dp percentage.

This can be a bit odd to get your head around but take a look, try it in your workbook and see if it gives you the desired results. Let me know if you need further help.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Yes, this has the desired effect but i'd also like to have -ve values in [red].
I'm still playing around with the suggested solution but cannot get the -ves to change colur.

The alternative was an actual formula which is rather long winded and requires an additional cloumn/cell
viz:

=IFERROR(IF(T6<-500%,"n/a",IF(T6>500%,"n/a",T6)),"n/a")

I prefer your solution as cell formatting but with [red] for negatives.

many thanks

Jason

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Hi Jason

Glad it's nearly there. Try this small change:

[Red][<=-999]"N/A";[>=999]"N/A";0.00%

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Computer says no.

Sorry, doesn't work.

Cheers

Jason

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Hi Jason

That's strange, that Custom Format works on my Excel 2010. Is it breaking or just not applying the format? Make sure that "Red" has a capital 'R'.

Here's a really good reference for Custom Formats:

http://simoncpage.co.uk/blog/2008/09/excel-custom-and-conditional-number-formatting/

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Tried copy pasting into field as prescribed.
the formatting works except for turning -ves into red
also tried capital "R" and moving the [Red] around but to no avail.
Even tried new book and started afresh, still doesn't want to turn -ves to red.

cheers again.

Jason

Edited on Tue 12 Aug 2014, 16:18

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Hi

I'm not sure what to suggest. I'm attaching my workbook where it's working to see if you can spot the difference.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Still no joy
even tried fprmat painter from your sheet to my sheet, and it works but goes back to black instead of red for -ves.

There be Gremlins in tha sheet.

i'll try rebooting and get back to you

THanks

Jason

### RE: Trying to eliminate unreasonable +ve and -ve percentage resu

Hi Jason

Any luck with this? I've searched around but can't find an answer to your issue. Is there other manual formatting / cell styles on the cell affecting it?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 20 Aug 2014: Automatically marked as resolved.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Adjusting the Elevation and Rotation in a 3-D Chart

For any 3-D chart you create, you can adjust the chart

View all Excel hints and tips