+ Reply to Thread
Results 1 to 6 of 6

Difficulty creating formula to complete all conditions

  1. #1
    Registered User
    Join Date
    03-12-2020
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Difficulty creating formula to complete all conditions

    Hello, I am attempting to write a formula in which the following conditions are true:
    I have 5 checkboxes with assigned/linked cells ($M$1, $N$1, $O$1, $P$1, and $Q$1).
    If none of the checkboxes are checked, the result needs to be 40.
    If ANY one of the five checkboxes is checked, the result needs to be 32.
    If ANY two of the 5 checkboxes are checked, the result is 24.
    If ANY three of the 5 checkboxes are checked, the result is 16.
    And if ANY four of the 5 checkboxes are checked, the result is 8.
    If all checkboxes are checked the result is 0.
    I have this formula so far, however it doesn't do everything correctly:
    =IF(Q1=TRUE,0,IF(P1=TRUE,8,IF(O1=TRUE,16,IF(N1=TRUE,24,IF(M1=TRUE,32,40)))))

    Any ideas on how to make this work?

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: Difficulty creating formula to complete all conditions

    Perhaps =40-COUNTIF(M1:P1,1)*8

  3. #3
    Registered User
    Join Date
    03-12-2020
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: Difficulty creating formula to complete all conditions

    Hi Pepe, would your formula be in place of what I have or in addition to what I have? Your formula by itself stays/equals 40 whether any or all checkboxes are checked or not.
    Thank you

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: Difficulty creating formula to complete all conditions

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    (I can't use checkboxes..)

  5. #5
    Registered User
    Join Date
    03-12-2020
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: Difficulty creating formula to complete all conditions

    Thank you, Pepe... that works great!

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: Difficulty creating formula to complete all conditions

    You're most 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. Difficulty Creating Chart Per Request
    By fyrewal in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-25-2019, 12:18 PM
  2. Difficulty in creating a maximization model for Solver
    By paulzov in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-26-2014, 01:53 AM
  3. Difficulty creating charts from complex data set
    By RosaNZ in forum Excel General
    Replies: 11
    Last Post: 08-23-2010, 04:50 AM
  4. Difficulty of creating a macro?
    By TylerLange in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2006, 06:02 AM
  5. I have had difficulty in creating user defined functions in Excel
    By DerbyJim1978 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] I have had difficulty in creating user defined functions in Excel
    By MichaelG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. I have had difficulty in creating user defined functions in Excel
    By MichaelG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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