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))))),"")

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 Removed)



"Rick" <(E-Mail Removed)> wrote in message
news:0E195F20-B132-448D-9802-(E-Mail Removed)...
> 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))))),"")

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.

"alondon" wrote:

> 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 Removed)
>
>
>
> "Rick" <(E-Mail Removed)> wrote in message
> news:0E195F20-B132-448D-9802-(E-Mail Removed)...
> > 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))))),"")
>
>
>