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.

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

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

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...)

You're welcome.