attempting complicated IF comparison with end goal easy for future users

[ALRFman]

Hello,

I have 2 worksheets with water quality data (Nutrients and Metals), on
worksheet with acceptable values for the different parameters measured
and one worksheet where I am attempting to look at these differen
parameters to say if they are acceptable or not.

There are 2 different labs and various dates along with the variou
parameters I am looking at.

This is a formula I have come up with, and it works for the first coupl
columns then stops.

=IF((Nutrients!$D$4:$D$24=$D4)*(Nutrients!$E$2:$AL$2="ET")*(Nutrients!$E$3:$AL$3=F$3),IF('acceptabl
limits'!$D$3>Nutrients!F$4,"GOOD","BAD")," ")

What I am trying to say is =IF((parameter in nuterients worksheet
parameter here) and (lab on nutrients worksheet = lab "ET") and (date o
nutrients worksheet = date here), then IF(parameter value on acceptabl
limits worksheet is greater than value on nutrients, then its good
otherwise bad) and its blank if it didn't work)

The idea is that there is data coming in every month, so I am trying t
make it easy for me (and future people) to see when something is wrong
with it easy to add the new incoming data.

Thank you for any advice

 

[GS]

ALRFman formulated on Wednesday :

Hello,

I have 2 worksheets with water quality data (Nutrients and Metals), one
worksheet with acceptable values for the different parameters measured,
and one worksheet where I am attempting to look at these different
parameters to say if they are acceptable or not.

There are 2 different labs and various dates along with the various
parameters I am looking at.

This is a formula I have come up with, and it works for the first couple
columns then stops.

=IF((Nutrients!$D$4:$D$24=$D4)*(Nutrients!$E$2:$AL$2="ET")*(Nutrients!$E$3:$AL$3=F$3),IF('acceptable
limits'!$D$3>Nutrients!F$4,"GOOD","BAD")," ")

What I am trying to say is =IF((parameter in nuterients worksheet =
parameter here) and (lab on nutrients worksheet = lab "ET") and (date on
nutrients worksheet = date here), then IF(parameter value on acceptable
limits worksheet is greater than value on nutrients, then its good,
otherwise bad) and its blank if it didn't work)

Actually, the above formula returns a space character for its final
'false' action, and so the cell will not be 'blank'. Even if it returns
an empty string (""), it still won't be 'blank'!

The idea is that there is data coming in every month, so I am trying to
make it easy for me (and future people) to see when something is wrong,
with it easy to add the new incoming data.

Thank you for any advice!

You have a 2nd IF attached to the 'true' part of the 1st IF. This will
only execute if all the factors in the 1st IF calculate to 'true'. I'm
thinking that your intent is better served using AND() instead of the
multiplication operator.


=IF(AND(Nutrients!$D$4:$D$24=$D4,Nutients!$E$2:$AL$2="ET",Nutrients!$E$3:$AL$3=F$3,'acceptable
limits'!$D$3>Nutrients!F$4),"GOOD","BAD")

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion