Feb 09 2019 05:19 AM
I'm not getting an error, I'm just not getting many results. I suspect its because of my greater than or less than zero requirements. The cells that contain the requirements are percentages so 1.0 is greater than zero and 0.1 is less than zero, correct? This formula worked perfectly before I added "OR" & "greater than or less than zero" requirements.
OLD formula that works perfectly:
=IF(AND($AB30<$AC30, $AD30<$AE30, $AF30<$AG30, $AH30<$AI30, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30),$Q30/$P30,"")
NEW formula that works but pulls little data:
=IF(AND( $AB30<$AC30,$D21>0, $AD30<$AE30,$F21>0, $AF30<$AG30,$H21>0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $D21<0, $AD30<$AE30,$F21>0, $AF30<$AG30,$H21>0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $F21<0, $AF30<$AG30,$H21>0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $AD30<$AE30,$F21>0, $H21<0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $AD30<$AE30,$F21>0, $AF30<$AG30,$H21>0, $J21<0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $F21<0, $AF30<$AG30,$H21>0, $J21<0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $F21<0, $H21<0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30
))))))),$Q30/$P30,"")
Feb 14 2019 04:57 PM
Ah, I just checked your code. I see where you are going with your sort question. D21 f21 and h21 should be $D$21 and so on. Wow I'm surprised I didn't see that. Great job Sergei!!!
Feb 14 2019 05:13 PM
The final corrected code below works perfectly. Thank you sir!!!
=IF(AND(
IF($D$21>0,$AB30<$AC30,1),
IF($F$21>0,$AD30<$AE30,1),
IF($H$21>0,$AF30<$AG30,1),
$AH30<$AI30,
$AJ30<$AK30,
$AL30<$AM30,
$AN30<$AO30,
$AP30<$AQ30,
(($D$21>0)+($F$21>0)+($H$21>0))
), $Q30/$P30,"")
Feb 15 2019 06:16 AM
Hi Greg - so far so good, you are welcome