Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Usining Yes/No (XP, SP2)

    Good Morning

    Is it possible to use a yes or no answer withing a formula? what I am trying to achieve is some thing like - Cell A17 = Do you require a pre 9am delivery, Cell B17 = Yes, Cell C17 = something to the effect that if B17,"Yes",C17,"35.00" but all I can get is C17 saying true or false

    TIA

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Usining Yes/No (XP, SP2)

    Hi Steve

    Can you clarify a few points.

    1) I see there is an entry for 35.00, is that because there is an extra charge before 9.00am
    2) If it is delivered after 9.00am is there a charge.


    The simplest way is (in C17)

    =if(B17="Yes", "35","")

    Tell me if I have the wrong end of the stick here or show us the firmula you are currently using to get the error
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Usining Yes/No (XP, SP2)

    Thanks Jerry

    That has done the job perfectly for the example when yes is selceted but it has thrown up another problem which you may be able to assist with

    I have a form with several option on it already from which somebody can choose what 'add ons' to the service they require, this is then totalled up - =B20+MAX(0,C4-10)*C24+D5+D6+D7+D8+D9+D10 (the D's are the existing add ons they can pick from, either 1 or many)

    For timed delivery options they can choose from pre 9am, pre 10am, pre noon etc. using your advice for the pre 9 am and adding +D12 to the above formula works when the Yes is inserted making D12=35 but returns a value error in B20 if the box is left blank.

    What I would like to achieve is to let the person type yes in any of the service boxes they require (there will only ever be one selection) and then have it added to the whole total

    Hope this makes sense

    Thank you

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Usining Yes/No (XP, SP2)

    Hi Steve

    I am feeling a dizzy looking at this as i cannot imagine the scenario very well, if there is a cut down version of you workbook that would be great but in the interim period the SUMIF function is telling me this is the answer, if you have a sheet available that would greatly assist
    Jerry

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Usining Yes/No (XP, SP2)

    You could make your formula:
    <code>=if(B17="Yes", 35,0)</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Usining Yes/No (XP, SP2)

    Thanks Rory that did the trick

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Usining Yes/No (XP, SP2)

    Sorry Jezza

    Don't want a bear with a chainsaw getting dizzy in the forest!

    Probably my description threw you as your response was almost there, using Rory's slightly updated version everything worked fine, I have attached a copy just to un-dizzy you.

    Now off to try and make it look better

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •