+ Reply to Thread
Results 1 to 6 of 6

Advice on multiple IF statements

  1. #1
    Registered User
    Join Date
    07-15-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    2

    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.

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,026

    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

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,651

    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.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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

  5. #5
    Registered User
    Join Date
    07-15-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    2

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

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Advice on multiple IF statements

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Advice & Answer for Multiple IF Statement
    By craigos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2013, 04:54 PM
  2. Multiple nested IF statements and AND statements
    By TonyGetz in forum Excel General
    Replies: 2
    Last Post: 12-14-2010, 03:07 AM
  3. Replies: 12
    Last Post: 05-15-2009, 08:38 AM
  4. Multiple copying of a Spreadsheet advice please
    By Dermot in forum Excel General
    Replies: 3
    Last Post: 07-30-2006, 08:37 AM
  5. Need multiple users advice
    By Nick Cartwright in forum Excel General
    Replies: 7
    Last Post: 07-02-2006, 01:25 PM

Bookmarks

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