# Advice on multiple IF statements

1. ## Advice on multiple IF statements

Dear all,

I'm currently putting together a mark sheet of record and collate student marks for next year and I'm having some trouble combining IF statements into a formula.

At the moment I have the following formula:

=IF(C7="AB",(20*C\$6)+(E7*E\$6),(C7*C\$6)+(E7*E\$6))/100

What this does is take any marks I enter into cells C7 and E7, applies any weightings in cells C6 and E6 (in this case 50%, as there are 2 components to this module) and then conflates this into a final mark (in this case in cell G7). Unless there is an AB entered into cell C7, (for background AB = absent and equates to a mark of 20 as this is the lowest mark available).

What I want to do is apply the above formula (Particularly the "AB" bit) to other cells, so that if there is an AB entered in cell E7 it also equates it to a mark of 20 and applies the weighting, etc. (Same as it currently does for cell C7)

The above formula works fine for my purposes so I have (unsuccessfully) tried to combine two of these with an =IF(AND formula to get the following:

=IF(AND(C7="AB",(20*C\$6)+(E7*E\$6),(C7*C\$6)+(E7*E\$6))/100,(IF(E7="AB",(20*E6)+(C7*C6),(E7*E6)+(C7*C6))/100))

This unfortunately now produces a #VALUE if I put AB in cells C7 and E7 or a FALSE if I enter numbers and I cannot see what I am doing wrong.

I am using Windows 7 and Excel 2010. I hope I have explained this well? If anyone requires further information then please just ask.

Any advice on where I may be going wrong here would be very gratefully received as my Excel skills are not exactly brilliant and I am learning as I go. (Also, I have been staring at this for far too long and it may be driving me a bit mad).

Many thanks in advance.  Register To Reply

2. ## Re: Advice on multiple IF statements

Try ``Please Login or Register  to view this content.``
But based on your description you might want to use an OR statement unless you need the AB in BOTH cells. The OR will work for you if either cell is AB  Register To Reply

3. ## Re: Advice on multiple IF statements

i think the problem is you have an AND instead of an OR.
yours =IF(AND(C7="AB",(20*C\$6)+(E7*E\$6),(C7*C\$6)+(E7*E\$6))/100,(IF(E7="AB",(20*E6)+(C7*C6),(E7*E6)+(C7*C6))/100))

In this, you are saying that if C7 = AB (a text) multiply and add a bunch of things including E7 which should then be a number value to work. Then you add into it the AND saying if E7=AB, and later multiply E7 by E6 and you can't multiple text by a value. You need some major alterations because i'm not even sure an OR can change it if you keep the E7*E6 in your statement. Posting a sample might help some people see what you need easier without guessing.   Register To Reply

4. ## Re: Advice on multiple IF statements

I would put the IF inside the equation, instead of the equation inside the IF...
i.e. Your original formula changes from
=IF(C7="AB",(20*C\$6)+(E7*E\$6),(C7*C\$6)+(E7*E\$6))/100
to
=((IF(C7="AB",20,C7)*C\$6)+(E7*E\$6))/100

Then you can do the same thing for E7

=((IF(C7="AB",20,C7)*C\$6)+(IF(E7="AB",20,E7)*E\$6))/100  Register To Reply

5. ## Re: Advice on multiple IF statements

Jonmo1 that appears to work perfectly! I will run some tests and apply it to a few sheets this afternoon. Many thanks to the other posters as well for helping me understand where I was going wrong for future formula fun!

Thanks again everyone, I think it's fair to say you have saved my sanity! (Temporarily at least...)  Register To Reply

6. ## Re: Advice on multiple IF statements

You're welcome.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1