Forum Discussion

Edie Ritterpusch's avatar
Edie Ritterpusch
Copper Contributor
Dec 01, 2017

Complex IF formula

I am using Excel 2013 and trying to create an IF forumula:

I have a drop down list of 32 items in Cell F9.  If they selection one of the first 16 I want to multiply Cell M9 by 1.  If they select one of the second 16 I want to multiply cell L9 by Cell M9.

 

I tried nesting them:

=IF(F9="Pre-Treat Asphalt",1*M9,IF(F9="Post-Treat Asphalt",1*M9,L9*M9)))

I also tried:

=IF(OR(F9="Pre-Treat Asphalt","Post-Treat Asphalt),"1*M9","L9*M9")

 

I'm not sure what I'm doing wrong. 

Formulas & Functions
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 01, 2017

    I advise you to depend on the source of that drop-down list in the logical test as shown in the below screenshot:

     

     

     

    This is the formula:

    =IF(F19<>"",IF(OR(F19=A1:A16),M19,L19*M19),"")

    To enter it, press Ctrl+Shift+Enter because it's an array formula.

     

    • Edie Ritterpusch's avatar
      Edie Ritterpusch
      Copper Contributor
      Dec 01, 2017

      Okay - it's not working completely.  Please see my attachment.  If the "Service Item" selected is O9:O24, then  "Subtotal" should equal the "Rate".  If the "Service Item" selected is O25:O41, then "Subtotal" should equal "Total Hours" times "Rate".

       

      With this formula, Subtotal will equal Rate only if O9 is selected from the drop down menu for "Service Item".  All other "Service Item" selections (O10:O41) give a Subtotal of "Total Hours" times "Rate".

       

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Dec 01, 2017

        What happened is:

        You forget to enter the formula by using the special keystroke shortcut: Ctrl+Shift+Enter.

         

        Select cell N9, press F2 to activate the edit mode, then don't hit Enter, but press Ctrl+Shift+Enter simultaneously, and do this every time you activate the edit mode.

        It's an array formula, you have to do this to force it to return the right result.

         

        After that, drag the formula down, but before you drag it, you have to make the range absolute to prevent it from changing during the drag, so just replace this: O9:O24 with this: $O$9:$O$24.

         

        Good luck

         

  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Iron Contributor
    Dec 03, 2017

    Given that your drop-down list source is in O9:O41 (which is 33 cells not 32), then you can use the MATCH function to return the position of your F9 selection within the source list.  If the position ios less than 17, then return whatever is in M9, otherwise return M9*L9.

    =IF(MATCH(F9,$O$9:$O$41,0)<17,M9,M9*L9)

    The 0 in the third argument of the MATCH function specifies an "exact match" which is OK because F9 must be selected from the list in $O$9:$O$41.

Resources