Help with function **SOLVED**

**Tonymac** · 08-21-2011, 07:36 PM

So, I am trying to make my job a little easier. This may get confusing so i am going to try to make it as clear as I can.

I have 4 cells. If the first one (A1) is less than 70 I want it to then check the next cell (A2). Again, if that one is less than 70 then I want the third cell to be checked (A3). If none are 70 and above then I want a phrase to appear in cell 4 (A4) "UNSAT". This is for testing purposes BTW.

Now, if A1 was equal to 70 but less than 90 I want cell A4 to say "SAT". If equal to or above 90 then I want it to say "SUP".

This would really help make things go much faster as I have 194 students that I have to process each cycle.

So

< 70 = UNSAT
= or > 70 but < 90 = SAT
and = or > 90 = SUP

The Students get three chances to pass and that is why I need the formula to check all three cells. Also, if the first cell is 70 or above then the other two need not be checked.

Thank you so very much. I am Semi new to excel and I have been trying to work with the IF but I cannot get it.

Tony

**abousetta** · 08-21-2011, 08:32 PM

Hi Tony and welcome to the Forum,

Maybe something like this:

=IF(AND(A1<70,A2<70,A3<70),"UNSAT",
IF(AND(A1>=70,A1<90),"SAT","SUP"))

abousetta

**Tonymac** · 08-21-2011, 10:08 PM

Thank you very much for the welcome and help. It worked out very well except for one minor detail. When I enter a 65 in A1, UNSAT come up (which is exactly what I need). But when I enter the second score of 70 in A2, for example, SUP comes up instead of SAT.

I think for some reason it is adding up the cells instead of individually analyzing. Could there be a fix or a way around this?

**abousetta** · 08-21-2011, 10:23 PM

For the second part of the equation, do you want it to check all three cells or just the first. Currently, it is looking only at A1.

abousetta

**abousetta** · 08-21-2011, 10:28 PM

Here's a modified version that looks at each cell in sequence:

=IF(AND(A1<70,A2<70,A3<70),"UNSAT",
IF(AND(A1>=70,A1<90),"SAT",
IF(AND(A2>=70,A2<90),"SAT",
IF(AND(A3>=70,A3<90),"SAT","SUP"))))

abousetta

**Tonymac** · 08-21-2011, 10:36 PM

That on is absolutely perfect!!! Thank you so very much. What a great resource this is!!! I wish I could rep you more.

Tony

**abousetta** · 08-21-2011, 10:51 PM

Hi Tony,

Thank for you for the feedback

and I am glad it worked out. I have learned a lot of this site and helping others is just a small way to give back.

Good luck.

abousetta

**Tonymac** · 08-21-2011, 11:06 PM

Well, just so you know, you helped 32 different academy's across the United States as I am sending this to all of them to streamline their work.

Thank you once again.

**abousetta** · 08-21-2011, 11:21 PM

It really was my pleasure.

Good luck.

abousetta

Help with function SOLVED

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View