+ Reply to Thread
Results 1 to 7 of 7

Tricky Problem

  1. #1
    Registered User
    Join Date
    07-23-2005
    Posts
    51

    Tricky Problem

    I have a spreadshhet with 3 columns,I wish to create a fourth column which
    carries out an action,if 2 conditions are met.

    col a has value that is greater than 6

    col c value is the appropriate value for the range in which values of b fall.

    col b contains numbers in the range 1 to 1000.

    The categories in col b and appropriate values in col c are shown below.

    If the values in col c are greater than the correct values I want no action taken,in col d.




    col b col c

    1 to 2 0.01

    2.02 to 3 0.02

    3.05 to 4 0.05

    4.1 to 6 0.1

    6.2 to 10 0.2

    10.5 to 20 0.5

    21 to 30 1

    32 to 50 2

    55 to 100 5

    110 to 1000 10

    an example sheet
    A B C D
    6.3 3.2 .05
    2.58 4.6 0.4
    0.24 4.2 0.3
    0.43 10 13
    4.14 21 11.5
    0.64 17.5 51.5
    0.26 17 63
    0.82 24 84
    1.00 27 113
    0.90 23 116
    0.38 38 132
    1.29 26 142
    0.90 26 264
    0.38 26 234
    0.75 32 388
    0.94 32 118

    here only the 1st row fulfils the 2 conditions.

  2. #2
    Arvi Laanemets
    Guest

    Re: Tricky Problem

    Hi

    What exactly must the function do. In Excel functions can't carry out any
    actions by definition - they only can display a result. To carry out an
    action (open some file, change the cursor position, activate a cell, write
    some value into cell, change the cell formatting, etc., is allowed for
    procedures only.

    --
    Arvi Laanemets
    ( My real mail address: arvil<at>tarkon.ee )


    "pytelium" <pytelium.1tnbsi_1123841184.0773@excelforum-nospam.com> wrote in
    message news:pytelium.1tnbsi_1123841184.0773@excelforum-nospam.com...
    >
    > I have a spreadshhet with 3 columns,I wish to create a fourth column
    > which
    > carries out an action,if 2 conditions are met.
    >
    > col a has value that is greater than 6
    >
    > col c value is the appropriate value for the range in which values
    > of b fall.
    >
    > col b contains numbers in the range 1 to 1000.
    >
    > The categories in col b and appropriate values in col c are shown
    > below.
    >
    > If the values in col c are greater than the correct values I want no
    > action taken,in col d.
    >
    >
    >
    >
    > col b col c
    >
    > 1 to 2 0.01
    >
    > 2.02 to 3 0.02
    >
    > 3.05 to 4 0.05
    >
    > 4.1 to 6 0.1
    >
    > 6.2 to 10 0.2
    >
    > 10.5 to 20 0.5
    >
    > 21 to 30 1
    >
    > 32 to 50 2
    >
    > 55 to 100 5
    >
    > 110 to 1000 10
    >
    > an example sheet
    > A B C D
    > 6.3 3.2 .05
    > 2.58 4.6 0.4
    > 0.24 4.2 0.3
    > 0.43 10 13
    > 4.14 21 11.5
    > 0.64 17.5 51.5
    > 0.26 17 63
    > 0.82 24 84
    > 1.00 27 113
    > 0.90 23 116
    > 0.38 38 132
    > 1.29 26 142
    > 0.90 26 264
    > 0.38 26 234
    > 0.75 32 388
    > 0.94 32 118
    >
    > here only the 1st row fulfils the 2 conditions.
    >
    >
    > --
    > pytelium
    > ------------------------------------------------------------------------
    > pytelium's Profile:
    > http://www.excelforum.com/member.php...o&userid=25521
    > View this thread: http://www.excelforum.com/showthread...hreadid=395262
    >




  3. #3
    Registered User
    Join Date
    07-23-2005
    Posts
    51
    I just want to write "yes" into d1 if both conditions are fulfilled,otherwise "no"

  4. #4
    Alan Beban
    Guest

    Re: Tricky Problem

    You stated "if 2 conditions are met"; but then you enumerated 3 conditions:

    1. Column A value > 6
    2. Column B is a number between 1 and 1000
    3. Column c is a number not greater than a a specified value

    So what's up?

    Alan Beban

    pytelium wrote:
    > I have a spreadshhet with 3 columns,I wish to create a fourth column
    > which
    > carries out an action,if 2 conditions are met.
    >
    > col a has value that is greater than 6
    >
    > col c value is the appropriate value for the range in which values
    > of b fall.
    >
    > col b contains numbers in the range 1 to 1000.
    >
    > The categories in col b and appropriate values in col c are shown
    > below.
    >
    > If the values in col c are greater than the correct values I want no
    > action taken,in col d.
    >
    >
    >
    >
    > col b col c
    >
    > 1 to 2 0.01
    >
    > 2.02 to 3 0.02
    >
    > 3.05 to 4 0.05
    >
    > 4.1 to 6 0.1
    >
    > 6.2 to 10 0.2
    >
    > 10.5 to 20 0.5
    >
    > 21 to 30 1
    >
    > 32 to 50 2
    >
    > 55 to 100 5
    >
    > 110 to 1000 10
    >
    > an example sheet
    > A B C D
    > 6.3 3.2 .05
    > 2.58 4.6 0.4
    > 0.24 4.2 0.3
    > 0.43 10 13
    > 4.14 21 11.5
    > 0.64 17.5 51.5
    > 0.26 17 63
    > 0.82 24 84
    > 1.00 27 113
    > 0.90 23 116
    > 0.38 38 132
    > 1.29 26 142
    > 0.90 26 264
    > 0.38 26 234
    > 0.75 32 388
    > 0.94 32 118
    >
    > here only the 1st row fulfils the 2 conditions.
    >
    >


  5. #5
    Registered User
    Join Date
    07-23-2005
    Posts
    51
    sorry,column B will always be a number between 1 and 1000.

  6. #6
    Arvi Laanemets
    Guest

    Re: Tricky Problem

    Hi

    "Write" or "display" - there is a huge difference, and I assume you meaned
    "display"

    In general, you need a simple IF-formula:
    =IF(LogicalCondition,"yes","no")
    The logical condition must be composite - 3 conditions must be true at same
    time:
    =IF(AND(Condition1,Condition2,Condition3),"yes","no")

    Condition1 - the value in column A must be > 6 (like 'A2>6')
    Condition2 - the value in column B must be >=1 and <=1000 {like
    'AND(B2>=1,B2<=1000)'}
    Condition3 - ... here are some problems with it!
    a)
    >> If the values in col c are greater than the correct values I want no
    >> action taken,in col d.

    'No action' means that nothing is displayed, or "no" is displayed?
    b) I see that:
    when in column B is 2, and in column C is 0.01, then "yes" must be
    displayed;
    when in column B is 2, and in column C is 0.02, then "no" (or "") must
    be displayed;
    when in column B is 3, and in column C is 0.02, then "yes" must be
    displayed;
    but what is displayed, when in column B is 3, and in column C is 0.01? From
    your explanation follows that "yes"?
    c) And what about cases, when in column B are values like 2.01, or 10.4, or
    105 ?
    Without sorting this out before, there is no way to give a right advice.


    Arvi Laanemets


    "pytelium" <pytelium.1tnk2d_1123851910.5086@excelforum-nospam.com> wrote in
    message news:pytelium.1tnk2d_1123851910.5086@excelforum-nospam.com...
    >
    > I just want to write "yes" into d1 if both conditions are
    > fulfilled,otherwise "no"
    >
    >
    > --
    > pytelium
    > ------------------------------------------------------------------------
    > pytelium's Profile:

    http://www.excelforum.com/member.php...o&userid=25521
    > View this thread: http://www.excelforum.com/showthread...hreadid=395262
    >




  7. #7
    Registered User
    Join Date
    07-23-2005
    Posts
    51
    Arvi

    Thanks very much for your help,I now have a solution to my problem,I am afraid I did not explain the problem very well.

    Pytelium

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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