attempting complicated IF comparison with end goal easy for future users
 

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('acceptabl e limits'!$D$3Nutrients!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)

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!

attempting complicated IF comparison with end goal easy for future users
 

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('acceptabl e
limits'!$D$3Nutrients!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$3Nutrients!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