trying eliminate unreasonable ve

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

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

Trying to eliminate unreasonable +ve and -ve percentage results

resolvedResolved · 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%

Please help.

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

Hi Gary and thanks for your swift reply.
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

Attached files...

Custom Format Negative Red.xlsx

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.


 

Excel tip:

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


Server loaded in 0.08 secs.