Help with nested if statements 1

[nonprogrammer]

Hello,

I am having a horrible time with 0ne of my formulas

it looks something like this

A B C D E F G H I J K L M N O P
1
2
3
4
5
in B1 I enter a value then C1 is a description D1 is a value that will be added or substracted from B1
E1 =B1-D1 F1 =B1+D1 then G1 through N1 are values that will be compared to E1 and F1 Here is wher my problem begins O1 should check if G1:N1 are less than E1 or if G1:N1 greater than F1 write "Yes" Then P1 if G1:N1 is greater than E1 and G1:N1 is less than F1 write "NO"

Please help with this I am a bit desperate I have been working on this for the past couple of days and is driving me a bit crazy

thank you!

 

[DirkStruan]

so O1 should be =if(or(max(G1:N1)<E1,min(G1:N1)>F1),"Yes","")
& P1 should be =if(or(max(G1:N1)<F1,min(G1:N1)>E1),"No","")?

D

 

[nonprogrammer]

KirkStruan,

Thank you so very much for the help, but when I write the formula it tells me that there is an error to accept with the error press yes and to decline press no

I clicked on yes and I got a 0(zero) on O1 then when I dragged it down it seems to work but not really because when I enter new values for g5:n5 the values of O1 and P1 do not change, any suggestions?

 

[DirkStruan]

Missed a second closing bracket:

have you got:

O1=if(or(max(G1:N1)<E1,min(G1:N1)>F1)),"Yes","")

&

P1=if(or(max(G1:N1)<F1,min(G1:N1)>E1)),"No","")?

 

[nonprogrammer]

DirkStruan,

I got another error,


The formula you typed contains an error.

* For information about fixing common formula problems, click Help
* To get assistance in entering a function, click OK, then click Function on the insert menu.
* If you are not trying to enter a formula, avoid using an equal sign (=) or minis sign (-), or precede it with a single quotation mark(').

OK Help

 

[AnotherHiggins]

If I may jump in here:

Dirk: Your parentheses (brackets to y'all in the UK) were correct the first time. When you added the second bracket, you closed out the IF statement after the OR statement, before giving the TRUE and FALSE arguments.

nonprogrammer: When you copied Dirk's formulas from his first post, did you include the question mark at the end of the second formula?

To be clear: if you type the following, you should be ok:
In O1:
[tab][COLOR=blue white]=if(or(max(G1:N1)<E1,min(G1:N1)>F1),"Yes","")[/color]

In P1:
[tab][COLOR=blue white]=if(or(max(G1:N1)<F1,min(G1:N1)>E1),"No","")[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

Sorry - going blind, the last post was rubbish and adding the closing bracket caused the last error. Please go back to the first post.

I just copied the original formulae I posted into a spreadsheet and got no errors at all. Did it highlight the original error when you tried it first time around? Can you see what changes it makes if you accept the default correction?

 

[nonprogrammer]

Ok it still kind of works
I Added a "No" and Changed the Yes for No

O1=if(or(max(G1:N1)<E1,min(G1:N1)>F1)),"No","Yes") and
P1=if(or(max(G1:N1)<F1,min(G1:N1)>E1)),"Yes","No")



here is what my values look like

  A   B   C   D   E   F   G   H   I   J   K  L  M  N  O  P
1    1       .25 .75 1.25 1   1   1   1   1  1  1  1 Yes No
This works
2    1        0   1   1   1   1   1                 Yes No 
this works
3    1        0   1   2   3   1                    yes Yes 
this does not work
4

5

It seems that whenever any of the numbers from g1:n1 are greater than e1 and lower than f1, it does not know what to do

 

[DirkStruan]

I think its the Min function messing things up. I guess some of the functions in G1:N1 return zeros?

 

[nonprogrammer]

some of the values could be zeros or they could be empty

 

[AnotherHiggins]

nonprogrammer:

Why don't you back up and think about what exactly you want in a formula. Think of all the possible scenarios you will run in to.

Then outline what it is, exactly, you are trying to accomplish.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

IF you need to ignore the zeros and the blanks you could use the Large and Small functions rather than Max and Min.

Large(G1:N1,1) will return the largest value

Small(G1:N1,countif(G1:N1,"=0")+1) will give you the smallest non zero entry.

Large and Small ignore blanks



Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[nonprogrammer]

I am a bit confused now. Sorry about that

ok so what I want to acomplish is that if any of the values in G1 through N1 are less than E1 and Greater than F1 O1 should say "No" and if the values in G1 through N1 are equal or between the values in E1 and F1 it should say "Yes"
for P1 I woluld just change the Yes to No and the No to Yes.

 

[AnotherHiggins]

nonprogrammer,

Does the following describe what you want?

In cell O1: If all values in G1:N1 are between or equal to E1 & F1, then YES, otherwise (that is, if any of the values in G1:N1 are not between E1 & F1) then NO.

In P1: Always the opposite of O1.

If that is what you want, then this will work in O1:
[tab][COLOR=blue white]=IF(AND(MIN(G1:N1)>=E1,MAX(G1:N1)<=F1),"YES","NO")[/color]
and in P1:
[tab][COLOR=blue white]=IF(O1="","",IF(O1="YES","NO","YES"))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[nonprogrammer]

Thank you so very much for all the help I also tried

O1=IF(MIN(G1:N1)>=E1,IF(MAX(G1:N1)<=F1,"Yes","No"),"No")

This also seems to work

especially thank you anotherhiggins