98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Unrealistic percentage values
Unrealistic percentage values
Resolved · Medium Priority · Version 2010
Tue 22 Jul 2014, 14:34Delegate Jason said...
Jason has attended:
Excel Advanced course
Excel Intermediate course
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - Formulas & Functions course
Unrealistic percentage values
I'm preparing data analysis of gross margins and want to avoid unrealistic values, eg anything over 500% positive and negative.
So far i have the following:
=-IFERROR(I37/H37,0).
can anyone please assist.
regards
Jason
Tue 22 Jul 2014, 15:22Trainer Doug said...
RE: Unrealistic percentage values
Hi Jason
Interesting question. How about this formula in a separate cell (eg C37)
=IF(OR(I37/H37>500%,I37/H37<-500%),"0",I37/H37)
It dispays the correct gross margin unless it is greater than 500% or less than -500%.
Best to format C37 to % format.
If you want to display a blank for the extreme gross marginsv then change "0" to "" in the formula.
Does that do what want?
Doug
Best STL
Tue 29 Jul 2014: Automatically marked as resolved.
Training information:
See also:
|
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing all your workbooks QuicklyHold the SHIFT key down and using the mouse click on the file menu, it will now now CLOSE ALL rather than close. This closes all workbooks down but still leaves the application open. |
