if and then using more than one cell

[rivk64]

How do i create if/then using multiple cells?
For example, I have data that i want to compare from D50 to D55 and i want to create and if and then statement saying that if all the values from D50 to 55 are greater than a number, then it passes. I wanted to do this for multiple groups of cells.

Any suggestions? Is there a way rather than individually listing out each cell with a if/then statement.

i hope im making sense...

thanks!

 

[thisoldman]

If all numbers in D50:D55 are greater than or equal to 10, return "PASS", otherwise, return "FAIL":
=IF(MIN(D50:D55) >= 10, "PASS", "FAIL")

 

[rivk64]

yes! that worked! another question!

so i have a group of cells that i want to include in this as well but all of the values in the cells say n.a
so currently it comes up as fail but really its not a fail.
so i wanted to include another if statement following that, where it goes if it says n.a, then it states n.a

im not sure if i have to reformat =IF(MIN(D50:D55) >= 10, "PASS", "FAIL") in order to make that work

 

[inactiveuserps07]

@rivk64

Try this:

=IF(MIN(D50:D55)>=10,"PASS",IF(AND(COUNTIF(D50:D54,"")=0,ISNUMBER(SUMPRODUCT((D50:D54)*1))),"FAIL","NA"))

It will only return "FAIL" if a number has been entered in all 5 cells in range D50:D54. If anything else is entered in the range, including null, it will return "NA."

 

[thisoldman]

@★ rivk64,

ErikTyler's solution may work for you. If it does not, I have some questions that will help us write a working formula.

When you write, in your second post, "n.a", do you mean the literal value n.a or do you mean the Excel error value #N/A ?

Is the tested range always in one column, or might it expand over two or more columns?
Will the tested range ever include anything other than a number, your "not available" indicator, or a blank?
What is a correct answer if any of the cells are blank?

 

[rivk64]

the values in the cell actually say "n.a.
its extracting that from a formula and if it cant be calculated, it will just say n.a. instead of #N/A

the tested range is always in one column but I wanted to drag this formula across multiple columns but those will be calculated individually, never including values from two different columns.

the data that I have, there is no blanks, just either numerical values or just n.a.

I hope this helps! thank you all for helping me.

 

[thisoldman]

If there are as many cells with numbers as there are rows in the test range, do the pass-fail test; otherwise, return "n.a".
=IF(COUNT(D50:D55) = ROWS(D50:D55), IF(MIN(D50:D55) >= 10, "PASS", "FAIL"), "n.a")