IF formula to accept or reject based on numbers entered.

[crabflinger]

Im trying to set up a spreadsheet that will indicate whether or not multiple
values are acceptable or rejectable for percentages entered for a single
line.

For example....

Percentages entered into A3, B3, and C3 needs to be between 3.0 and 5.0.....
if the percentage entered is 2.9 or below / 5.1 or above in any cell, it will
automatically fill in E3 with an "X". And if any percentage entered is
between 3.0 and 5.0 it will fill in D3 with an "X".

Something like this. (as best as I could describe it)

A B C D
E
1 Element 1 Element2 Element3
2 3.0 - 5.0 3.0 - 5.0 3.0 - 5.0 A R
3 2.8 4.5 4.9 X
4 3.5 4.1 3.9 X

I was able to accomplish this a long time ago, however it was several years
ago and I dont have the first clue as to how I managed to accomplish it
before.

Can anyone please help?????

 

[crabflinger]

I could really make this work if I were trying to get an answer of ACCEPT or
REJECT based on a percentage of anything outside of 3.0 to 5.0 being
rejectable.

The problem that I am having is that I have to consider multiple ranges and
elements.

Take for example that I have a piece of alloy steel.

The specification of that particular alloy says it should have:
CHROME 08.00 - 10.00
MOLYBDENUM 00.09 - 01.10
MANGANESE 00.30 - 00.60

Im trying to create a preset form so that when we perform a chemical
analysis of the alloy, my inspectors can simply input the percentages they
find. Based on the formulas I have entered, the form will automatically tell
them whether or not their measurements are acceptable or rejectable.

If every element reading falls within its own preset guidelines, the end
result will indicate: ACCEPT.

But if any one reading for that particular element falls outside of the
ranges required, it will indicate: REJECT.

I understand this seems difficult. I should know, Ive been trying to figure
it out on my own for a little over a week, haha.

But nonetheless, THANK YOU very much

:

This can be done with IF formulas......

For example, this one will give you an "x" if any cell in the row is below
3, or if any cell in the row is above 5
=IF(MIN(A3:C3)<3,"x",IF(MAX(A3:C3)>5,"x",""))

What result would you like to see if you had mixed conditions in one
row...ie, 2 4 6, or 4 4 6 or 2 4 4.........

Vaya con Dios
Chuck, CABGx3

 

[Luke M]

Perhaps you could consider changing the way your form is setup then.
Using your alloy example, have in A1 "CHROME", C1 = "MOLYBDENUM" and E1=
"MANGANESE"
Below, in A2 and B2 have 8 and 10 respectively.
C2 & D2 = .09 and 1.1
E2 & F2 = .3 and .6

For formatting purposes, hide columns B, D, and F. (or not, it's purely a
visual thing, if you want these values displayed or not.)

Now, your inspectors could input their findings into cells A3, C3, and E3.
formula in your "Findings column" (which is cell G3)
=IF(AND(A3>=A$2,A3<=B$2,C3>=C$2,C3<=D$2,E3>=E$2,E3<=F$2),"ACCEPT","REJECT")

 

[crabflinger]

I actually have two versions of the form, and my customers are "requesting"
(meaning requiring) that I stick to the two approved formats.

Is there anyway you could contact me at: (e-mail address removed) so that I
could send them to you, and perhaps you would have a better idea of what I am
needing to accomplish?

Thank You very much.

 

[Rick Rothstein]

Try these formulas in these cells...

D3: =IF(SUMPRODUCT((A3:C3>=3)*(A3:C3<=5))=3,"X","")
E3: =IF(AND(A3<>"",D3=""),"X","")

and then copy them down.

 

[crabflinger]

Something like this (if it works)

<a
href="http://s66.photobucket.com/albums/h270/space_pikey/?action=view&current=WORKSHEETEXAMPLE.jpg"
target="_blank"><img
src="http://i66.photobucket.com/albums/h270/space_pikey/WORKSHEETEXAMPLE.jpg"
border="0" alt="Photobucket"></a>