Amending an if statement

**isolation123** · 03-04-2008, 08:39 AM

hi guys does anyone out there know why this formula does not work:

=IF(H29=>70,"1st",IF(H29>=60AND>=69,"2:1",IF(H29>=50AND>=59,"2:2",IF(H29>=40AND<=49,"3rd"))))

The formula is to work out the class of an honours degree for a certain student.

thanks guys.

**NBVC** · 03-04-2008, 08:45 AM

Should probably be:

Please Login or Register  to view this content.

or more simply:

Please Login or Register  to view this content.

**EdMac** · 03-04-2008, 08:46 AM

Hi,

Please Login or Register  to view this content.

You don't need AND. The logic is: if more than 70 1st, else if more than 60, 2nd etc

change to

Please Login or Register  to view this content.

Ed

**isolation123** · 03-04-2008, 08:55 AM

thanks guys it works now!!!

**isolation123** · 03-05-2008, 09:41 AM

hi guys i have an if statement that does not work here it is:

=IF(360>J10>300,"pass","Fail")

the if statement is to work out if a student has got a degree or not they have to have between 300 and 360 to be awarded a degree otherwise they fail.

thanks guys!

**arthurbr** · 03-05-2008, 09:47 AM

=IF(and(j10<360,j10>300),"pass","Fail")

**isolation123** · 03-11-2008, 11:49 AM

hi guys i am creating a system for offering degree's and honours degree's. i need it so when the subject dissertation is taken they can be awarded an honours but when it is not taken they cannot i am using this formula which works to work out the honours at the moment =IF(H5="Fail","",IF(G29>=70,"1st",IF(G29>=60,"2:1",IF(G29>=50,"2:2",IF(G29>=40,"3rd",)))))

is there anyway i can do this by adding anything to the formula i have already got?

thanks guys i appreciate it alot!

**Mark@Work** · 03-11-2008, 02:18 PM

Is this the sort of thing you are thinking of?

=IF(OR(H5="Fail",D5="no dissertation"),"",IF(G29>=70,"1st",IF(G29>=60,"2:1",IF(G29>=50,"2:2",IF(G29>=40,"3rd",)))))

**isolation123** · 03-12-2008, 09:38 AM

thanks very much for your reply but please could you explain the D5 bit??

**Mark@Work** · 03-12-2008, 12:35 PM

D5 is just a cell I chose at random to hold the information of if the dissertaion was handed in or not.
Where this iformation is actually held (and what format it is held in) is entirly up to you.

Mark.

**isolation123** · 03-12-2008, 02:30 PM

would it be possible to do the same formula but have a range of cells where the dissertation could be??

**isolation123** · 03-12-2008, 03:06 PM

Hi guys

i have this formula below which works but where it says IF(B45="Dissertation" instead of b45 i need a range of cells where the dissertation could be.

=IF(H5="Fail","",IF(B45="Dissertation",IF(C45>39,IF(F37>=70,"1st",IF(F37>=60,"2:1 ",IF(F37>=50,"2:2",IF(F37>=40,"3rd",)))))))

thanks very much for your help!

**Mark@Work** · 03-12-2008, 03:08 PM

How is your sheet arranged?
In particular how are you entering the information about the dissertations?
Why is H5 "pass" or "fail"? this is why I chose D5 to go with H5, I might have chosen D29 to go with G29...

Do you have a different student on each row, with the names in column A, The scores for course 1 in B, course 2 in C ..., total scores in G, Pass/Fail in H?
Add disertation/no disertation in a spare column (lets use XX)

=IF(OR(H29="Fail",XX29="no dissertation"),"",IF(G29>=70,"1st",IF(G29>=60,"2:1 ",IF(G29>=50,"2:2",IF(G29>=40,"3rd",)))))

Mark.

**isolation123** · 03-12-2008, 03:10 PM

Originally Posted by Mark@Work

How is your sheet arranged?
In particular how are you entering the information about the dissertations?
Why is H5 "pass" or "fail"? this is why I chose D5 to go with H5, I might have chosen D29 to go with G29...

Do you have a different student on each row, with the names in column A, The scores for course 1 in B, course 2 in C ..., total scores in G, Pass/Fail in H?
Add disertation/no disertation in a spare column (lets use XX)

=IF(OR(H29="Fail",XX29="no dissertation"),"",IF(G29>=70,"1st",IF(G29>=60,"2:1 ",IF(G29>=50,"2:2",IF(G29>=40,"3rd",)))))

Mark.

i have posted another question which should explain it. see edited if statement on excel general or just look for posted by isolation123

**isolation123** · 03-12-2008, 03:17 PM

i have this formula below which works but where it says IF(B45="Dissertation" instead of b45 i need a range of cells where the dissertation could be.

=IF(H5="Fail","",IF(B45="Dissertation",IF(C45>39,I F(F37>=70,"1st",IF(F37>=60,"2:1 ",IF(F37>=50,"2:2",IF(F37>=40,"3rd",)))))))

thanks very much for your help!

**EdMac** · 03-13-2008, 04:45 AM

Hi,

=IF(H5="Fail","",IF(B45="Dissertation",IF(C45>39,I F(F37>=70,"1st",IF(F37>=60,"2:1 ",IF(F37>=50,"2:2",IF(F37>=40,"3rd",)))))))

use OR and use the possible cell locations

Please Login or Register  to view this content.

Ed

**isolation123** · 03-13-2008, 06:16 AM

thanks ed for you great reply i now have this huge formula
=IF(H5="Fail","",IF(OR(B22="Dissertation",B23="Dissertation",B24="Dissertation",B25="Dissertation",B26="Dissertation",B27="Dissertation",B28="Dissertation",B29="Dissertation",B30="Dissertation",B31="Dissertation",B32="Dissertation",B33="Dissertation",B34="Dissertation",B35="Dissertation",B36="Dissertation",B37="Dissertation",B38="Dissertation",B39="Dissertation",B40="Dissertation",B41="Dissertation",B42="Dissertation",B43="Dissertation",B44="Dissertation",B45="Dissertation"),IF(C45>39,IF(G29>=70,"1st",IF(G29>=60,"2:1 ",IF(G29>=50,"2:2",IF(G29>=40,"3rd",)))))))

Would it be possible to have a thing were it says no dissertation if there is no dissertation present??

**VBA Noob** · 03-13-2008, 06:21 AM

You can replace

B22="Dissertation",B23="Dis sertation",B24="Dissertation",B25="Dissertation",B 26="Dissertation",B27="Dissertation",B28="Disserta tion",B29="Dissertation",B30="Dissertation",B31="D issertation",B32="Dissertation",B33="Dissertation" ,B34="Dissertation",B35="Dissertation",B36="Disser tation",B37="Dissertation",B38="Dissertation",B39= "Dissertation",B40="Dissertation",B41="Dissertatio n",B42="Dissertation",B43="Dissertation",B44="Diss ertation",B45="Dissertation")

with

COUNTIF(B22:B46,"Dissertation")>0

VBA Noob

**isolation123** · 03-13-2008, 06:30 AM

hi there

please could you explain that a bit more in depth as i need the dissertation to be pass i.e over 40 you can see this by looking at the cell next to where ever the dissertation is.??????

**EdMac** · 03-13-2008, 06:31 AM

Hi,

As VBA Noob says, because the cells are all adjacent, the formula can be simplified.

You also need to take out the OR.

Please Login or Register  to view this content.

.

is a valid arguement and can be incorporated somewhere

Ed

**isolation123** · 03-13-2008, 06:35 AM

why the =0 ??

**EdMac** · 03-13-2008, 06:48 AM

Would it be possible to have a thing were it says no dissertation if there is no dissertation present??

0= if there is no instance of "dissertation" - isn't that what you asked?

This would need to be fitted in the formula - I presume that it would affect the class of degree.

It would be very helpful if you could write down the 'Rules' for deciding the degree class

e.g. If a dissertation and if the mark is X% then this, if no dissertation and the mark is Y% that that.

Add to this where the cells are that the information comes from - ideally in the form of a sample sheet showing the layout - and this problem should be easily solved.

You know exactly what you want but we are having to second guess it most of the time.

Ed

**isolation123** · 03-13-2008, 07:12 AM

the sample does not show that much because the print screen wasnt uploading sorry. but anyway you can see the module codes where the dissertation could be in any position. the only way the student can get a honours classification is to take the dissertation subject and get over 40% mark so i need to add that to the formula shown in previous questions?

hope that helps?

**EdMac** · 03-13-2008, 07:35 AM

Your word doc fails to download for me.

Can you ZIP a copy of the spreadsheet and send that?

Ed

**isolation123** · 03-13-2008, 07:43 AM

here is the sample zip file

**EdMac** · 03-13-2008, 07:56 AM

Possibly

In H8

Please Login or Register  to view this content.

BUT

Col B refs will need to be changed to those where the word 'Dissertation' appears as these cells have module Descriptions in them.

Ed

**isolation123** · 03-13-2008, 08:00 AM

i think that works thanks mate you know your excel!!

**EdMac** · 03-13-2008, 08:02 AM

That's good.

Post back if you need anything else.

Ed

Amending an if statement

LinkBack

Thread Tools

Rate This Thread

Display

Formula Syntax assistance

thanks

complex if statement