If w/ Nested OR and AND driving me crazy

[bcochra]

I thought I had figured out how to use nested OR and AND with the IF function. I am trying to create a sheet to easily identify classes based on various test scores. There are various combinations that could qualify a student for a class; however, the formula is returning "Yes" for students whose scores do not meet criteria (see pics). I don't know what I am missing. Please help.

MAT 121.png

ENG 121.png

[samba_ravi]

I think the AQ674 Contains a text value like space(" ")
or got the value by using formula which is used "" to avoid errors
Even though AQ674 is looking like blank cells actually it is not blank

[Khalidngo]

Hi,

Wrap your all cells with N, something like:

=IF(OR(N(A1)>=23,N(B1)>=85,N(C1)>=500),"Yes","No")

Regards,

[pjwhitfield]

Hi,

Wrap your all cells with N, something like:

=IF(OR(N(A1)>=23,N(B1)>=85,N(C1)>=500),"Yes","No")

Regards,

Its important to remember though that when you use N() you make it far more difficult to spot errors and therefore can end up with incorrect results

eg the formula:


=IF((A1*B1*C1/D1) < 10, TRUE,FALSE)

used against
A1 = 2
B1 = 3
C1 = 4
D1 = 5

returns TRUE

IF however C1 was "A" then it would return
#VALUE

If you use
=IF((N(A1)*N(B1)*N(C1)/N(D1)) < 10, TRUE,FALSE)

however that would also return TRUE so it would be difficult to spot a problem (especially if the data is elsewhere and not in general view).

[Tony Valko]

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

[bcochra]

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

UPDATE:

Sorry, I didn't know about the .png files

Here is the formula and data:

=IF(OR(AND($AL674>=95,$AK674>= 80), AND($AG674>=18,$AH674>= 17),AND($AL674>=95,$AH674>=17),AND($AK674>=80,$AG674>=18),($AP674>=470)),"Yes","No")

AG=14
AH=17
AK=66
AL=82
AP=0

It should return “No”, but it is returning “Yes”.


When I tried to put this in a new sample sheet to upload, the formula worked correctly and returned "No"

The test scores are being pulled using a VLOOKUP and they are being pulled correctly. They are formatted as numbers. I am lost on why the formula would work in new sheet but not original.

Unfortunately, due to FERPA and the size, I cannot upload the original.

[AliGW]

You can post a cut-down desensitised version of your workbook.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[bcochra]

Sample File Attached

Issue is resolved if I replace VLOOKUP results by retyping what they return, but I don't understand why.

[AliGW]

I don't understand the spreadsheet - where exactly is the VLOOKUP formula that isn't working? Or am I not understanding?

[bcochra]

The VLOOKUP is in cells AD:AQ; however, it is working fine and returning the correct results. But if I type over the VLOOKUP results, I get the correct result for the IF functions in I:AB.

If I leave the VLOOKUP results alone, I:AB does not return the correct results

[pjwhitfield]

try putting VALUE before each of the VLOOKUPs eg AD2 should show

=VALUE(VLOOKUP($D2,'Student Data Table DON''T TOUCH'!$A$1:$R$5,7,FALSE))

I think that currently your VLOOKUPs are returning TEXT rather than numbers.

[bcochra]

try putting VALUE before each of the VLOOKUPs eg AD2 should show

=VALUE(VLOOKUP($D2,'Student Data Table DON''T TOUCH'!$A$1:$R$5,7,FALSE))

I think that currently your VLOOKUPs are returning TEXT rather than numbers.

This appears to solved it. Thank you so much.