Conditional Functions
I have data in 2 columns that I want to work with. In one column (O) is a
percentage that sometimes equals 0. In the other (P) is a percentage that never equals 0. Now in the third column (Q), I have the function =(P2-O2)/O2, but sometimes, when O=0%, the function doesn't work. So I changed it to say =IF(OR(O2=0%,(P2-O2)/O2200%),200%,(P2-O2)/O2). I never want the Q column to be more than 200%. It works for numbers that quotient to more than 200%, but for functions where O=0%, it says #DIV/0!, where it should just say 200%, as I've instructed it. What should I do? |
Conditional Functions
The result #DIV/0! appears to be from the "OR" part of the formula. OR(O2=0%,(P2-O2)/O2200% I.e. in evaluating the second part of the OR it gets the fault ,........ You could change the IF formula to a triple nested IF =IF(O2=0%,200%,IF((P2-O2)/O2200%,200%,(P2-O2)/O2)) -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=484614 |
All times are GMT +1. The time now is 03:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com