Circular Express when $ added

[Guest]

The following express gave the condition of 100% when $ was missing, and when
added it gave a 0%, any reasons why?

=IF(OR($G5="C",$G5="M"),IF(OR($O5=0,SUM($I5-$J5=0)),"",IF($G5="M",0.8,IF(YEAR($B5)=$AU$3,1,IF(OR($F5="Full",$F5="BMP",$F5="CBC",$F5="HFP",$F5="LP",$F5="RVP",$F5="UA"),1,IF(OR(AND($H5="D",$A5>=RollUp!$AX$15,$C5>=RollUp!$AY15),AND($H5="S",$A5>=RollUp!$AX$16,$C5>=RollUp!$AY16)),1,0.8))))),"")

IF(OR(AND($H5="D",$A5>=RollUp!$AX$15,$C5>=RollUp!$AY$15),AND($H5="S",$A5>=RollUp!$AX$16,$C5>=RollUp!$AY$16)),1,0.8))))),"")

 

[alondon]

Rick,

I have untangling many an Excel workbook with equations like these. It s
not much fun and it is really time consuming - and expensive.

If you expect anyone else to be able to help you, learn how to use named
ranges, decision trees and User Defined Fuctions (UDF).

I have not the slightest idea of what you are trying to accomplish here?
But, the compound AND statements at the end of your expression seem to
contain a logic error.

Sorry to be such a grump.

Allan P. London, CPA
(e-mail address removed)

 

[Guest]

Sorry about the tangled logic, but this workbook has about 20 sheets to it
the code I gave here is just for the accumulative analyist sheet. with
columns going out to BZ356. This is a medical tracking program in Excel and I
am only a beginner in VB so most of the code is in Excel. Thanks for
indicating a logic error in the compound statement, I will have to study that
one a little more carefully.
Rick.