Simple Formula

Posted on 2011-10-17
Last Modified: 2012-05-12
EE Professionals,

I am attempting to control the parameters of two cell inputs without writing a macro or putting in error handling code.

I have two contiguous cells where they start out with the condition "n/a" (i.e. C5 and D5).  I am looking for a data validation "custom" formula for C5 that says, the input number must be a whole number from 1 to 10 but must also be lower then any number put in D5 which is populated after C5 (i.e. it still has "n/a" in Cell D5 until after C5 is chosen.   D5 must be a number equal to or higher then C5 but less then 11 (i.e. 1-10 whole number) but may also be valid if it has "n/a" in the cell. Remember that both cells start out with "n/a" in them and will be reset to that so the validation must also be able to treat "n/a" without an error condition and I believe it automatically assigns a value of -0- to it.

Your help on this is much appreciated.

Question by:Bright01
    LVL 50

    Expert Comment


    try these two custom data validation formulas:

    C5 =IF(C5="n/a",TRUE,AND(C5<10,C5>0,MOD(C5,1)=0,C5<D5))
    D5 =IF(D5="n/a",TRUE,AND(D5+C5<11,D5>=C5,MOD(D5,1)=0))

    If D5 must be higher than C5, it would make sense to limit C5 to a maximum value of 5, otherwise there will be no valid option to be entered into D5.

    cheers, teylyn

    Author Comment


    Greetings!  Two things;

    1.) C5 could be 6 and D5 could be 7.  Or C5 could be 9 and D5 could be 10.  Either cell can be between 1-10 except for the fact that D5 always has to be either n/a or higher then C5, up to 10.  What I'm also trying to avoid is the case where someone changes C5 after putting in numbers for C5 and D5 and then C5 must be less then D5.  Make sense?  That's why I think the limit is still 1-10 for both cells.

    2.) I tried to reach you by your outside email address and got bounced each time.  In AUZ but will be unable to get to NZ on this trip.

    LVL 50

    Accepted Solution


    try these two

    C5 =IF(C5="n/a",TRUE,AND(C5<=10,C5>=0,MOD(C5,1)=0,C5<D5))
    D5 =IF(D5="n/a",TRUE,AND(D5<=10,OR(D5>C5,C5="n/a"),MOD(D5,1)=0))

    cheers, teylyn

    Author Closing Comment

    EXCELLENT!  Thank you for the quick solution.  It "works like a charm".


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now