+ Reply to Thread
Results 1 to 6 of 6

Formula determined by Yes/No Dropdown

  1. #1
    Registered User
    Join Date
    06-05-2020
    Location
    Cleveland, Ohio
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    6

    Formula determined by Yes/No Dropdown

    Hi All -

    I am EXTREMELY new to Excel and am having trouble with configuring what I need this job aid to do:

    The attached sheet is to be used as an aid to determine pricing for a contract year with an annual percentage increase. The trick is, sometimes a particular coverage of "Cleaning" (Column C) does not receive the increase (I:1) that all the other coverage types receive.
    I want users to be able to select from the drop down in (I:3) if the increase applies to the "Cleaning" coverage type but I cannot figure out how to work this or where to put any applicable formulas.

    I think I need a formula in Column I that basically says the following:

    IF I3 = YES, then determine the value of Column F x the increase percentage of (I:1), IF I3 = NO, then copy the annual value in Column F.

    Any help or input?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula determined by Yes/No Dropdown

    Hi,
    1. Does your formula need to be per line? Sometimes the cleaning increase will apply and sometimes not?
    If so - you need to open a column and add YES/NO per line.
    2. where do you want your formula to be? Is it instead of an existing one or do you want to create a new claculation on top of what you already have?

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Formula determined by Yes/No Dropdown

    Please Login or Register  to view this content.
    If per row, then replace $I$3 to refer to the yes/no cell in the current row...
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    06-05-2020
    Location
    Cleveland, Ohio
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    6

    Re: Formula determined by Yes/No Dropdown

    1. It would only apply to the "Cleaning" lines.
    2. I think I would need to be instead of an existing calculation in Column I, but I'm not entirely sure it is possible? This would only apply to any cells that say "Cleaning" in column C, not per line. So basically:

    IF column C says "Cleaning" and I:3 says "YES" , multiply the value of F*I:1 to determine the value of I.
    IF column C says "NO" and I:3 says "NO" , copy the value of F.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Formula determined by Yes/No Dropdown

    Try:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-05-2020
    Location
    Cleveland, Ohio
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    6

    Re: Formula determined by Yes/No Dropdown

    I was able to use this in conjunction with the existing formula in Column I to apply to all situations I needed. Thank you so much for the help and your patience!

+ 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. [SOLVED] SUMPRODUCT with Range Determined by Formula
    By andrewc in forum Excel General
    Replies: 3
    Last Post: 01-28-2020, 03:34 PM
  2. Replies: 1
    Last Post: 09-08-2014, 04:03 AM
  3. Replies: 2
    Last Post: 09-08-2014, 01:36 AM
  4. [SOLVED] Construct a formula on a pre-determined cell for a pre-determined duration of cells
    By Shaun Gemiver in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 11:14 AM
  5. Adding Value of Cells, but Cell Determined by Dropdown List
    By concretetsunami in forum Excel General
    Replies: 4
    Last Post: 03-09-2010, 04:19 PM
  6. Replies: 5
    Last Post: 08-14-2006, 01:50 PM
  7. Applying same formula on pre-determined cells
    By AndyOne in forum Excel General
    Replies: 3
    Last Post: 04-08-2005, 10:42 AM

Tags for this Thread

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