IIF statement with two criteria

[paulfields4]

I have all these fields some have negative numbers. If one of the fields does I want it to display a zero. If it does not I want it to display the consultfee assoicated with that invoice. I am trying to do this with the following statement.

=IIf(Nz([1-30 Days]) And Nz([31-60 Days days]) And Nz([61-90 Days days]) And Nz([91-180Days days]) And Nz([180-360Day days]) And Nz([Over360Day days])>=0,0 Or IIf(Nz([1-30 Days]) And Nz([31-60 Days days]) And Nz([61-90 Days days]) And Nz([91-180Days days]) And Nz([180-360Day days]) And Nz([Over360Day days])<=0,([ConsultFee])))

 

[xenou]

What kind of data is in those fields for days? Numbers? True/False? Text?

This formula looks very suspicious!

 

[Joe4]

Isn't this the same as (or part of) this question here: http://www.mrexcel.com/forum/micros...ne-field-displays-negatives-toturn-zero.html?

If so, please keep it to one thread. If there is something you do not understand, or need to expand upon, just continue on in the same thread.

 

[xenou]

This formula looks very suspicious!

Note that my main concern was the using of comparison operators and a number (greater than or equal to zero) with potentially text data, which probably would give results that aren't correct. Also IIF may be a less than optimal choice here.