1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Need help writing simple Excel formula

Discussion in 'Business Applications' started by Harry32, Feb 9, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. Harry32

    Harry32 Thread Starter

    Joined:
    Jul 28, 2009
    Messages:
    124
    I'm trying to write a formula for a spreadsheet in Excel 2010, but I'm obviously not doing it correctly because Excel just recognizes the formula as text. What I want is a formula in cell E6 that produces a value of 30 times the value of Cell C6, but only if the value of Cell D6 ≥ 0.


    What I entered as a formula in Cell E6 is: IF(D6≥C6),E6=30*C6


    I suspect it needs more or fewer parens or commas or something. Can anyone straighten me out? Many thanks.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    you need to add an = infront in order for excel to recognise as a formula or calculation

    =IF(D6≥C6), E6=30*C6 ,false) - although the formula is incorrect
    Also you need to add the false section - what do you want if false ?

    =IF ( Test , True , False )
    Evaluates the Test and if the result is true - then it carries out whatever you put in true , if the test is false it carries out the false part


    Put this into cell E6
    =IF( D6 >= 0 , 30*C6 , false )

    The test is
    Is D6 greater than or equal to zero ( Note the = follows the greater than sign - must be in that order to work >=
    If that is true and D6 is equal to or greater than a zero - it will do the true part - 30*c6
    But blank spaces are also considered zero - so if D6 has no content that will also be considered true
    If D6 is less than zero - the result will be false and in my example - False will appear in the cell
    if you want a blank cell instead then
    =IF( D6 >= 0 , 30*C6 , "" )
    otherwise , as mentioned above - what do you want if D6 is less than zero
     
  3. Harry32

    Harry32 Thread Starter

    Joined:
    Jul 28, 2009
    Messages:
    124
    Thank you, Wayne. I've worked extensively with Excel ever since it was created (about 25 years ago?) and use it constantly at present, but I've never had occasion to write a conditional formula until now. I'm uncertain about the need for embedding the words TRUE and FALSE in a conditional formula and other details of the formula structure. If I could see a formula that performs the following and demonstrate it on my spreadsheet, I might understand the process better and learn to write other similar formulas.


    I want to place a formula in Cell E6 that produces a value of 30 times the value of Cell C6, but only if the value of Cell D6 is ≥ 0. If Cell D6 is < 0, Cell E6 should remain empty.


    What would such a formula look like? Thank you –
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    As i posted
    =IF( D6 >= 0 , 30*C6 , "" )

    =IF ( Test you want to carry out , The formula or value you want if the test result is True , The formula or value you want if the test result is False )

    So the Test in your case is
    Is the cell D6 Greater than or equal to zero
    D6 > = 0
    If that test is TRUE then do the True section of the formula
    30 * C6
    If that test is TRUE then do the True section of the formula
    for a blank we use ""

    However , your test
    D6 >= 0
    Will also be true if the cell D6 is blank - as Excel sees blanks as zero in formulas

    So we could avoid that by adding another condition to the Test
    in this case an AND

    AND( D6 >=0 , D6<>"")

    =IF( AND( D6 >=0 , D6<>"") , 30*C6 , "" )

    hope that helps
     
  5. Harry32

    Harry32 Thread Starter

    Joined:
    Jul 28, 2009
    Messages:
    124
    Wayne, with your help I've managed to create a semi-automatic score sheet for bridge (copy attached). It's not intended to replace the typical manual score sheet that everyone uses for play, but I seized on the idea as a way to start learning how to write conditional formulas for Excel worksheets. I'm pleased with the result despite its limitations.


    I still have an awful lot to learn with respect to conditional Excel formulas. Where can I find a list of commonly used symbols and abbreviations for conditional formulas, e.g., >=, <>, "", etc. Is the same as >=? What introductory words beside IF and AND are commonly used? Words like UNLESS or UNTIL? Where can I find sequencing rules for formulas having multiple conditions?


    Thanks very much.
     

    Attached Files:

  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    your very welcome

    There are quite a few websites which will provide tutorials and detail on excel functions - i dont use any particular site
    This is quite a good site
    http://www.cpearson.com/Excel/Topic.aspx


    If you just use the functions in excel you will see how they work - and clicking on the Fx next to the formula bar will also bring up the functions and there is help for each of those

    Some of the logical functions
    AND() OR() NOT() XOR()

    Yes

    > Greater than
    < Less than
    >= Greater than or equal
    <= Less than or equal
    "" set the cell blank
    <> not equal to
     
  7. Harry32

    Harry32 Thread Starter

    Joined:
    Jul 28, 2009
    Messages:
    124
    Thanks for the additional information. I think I can take it from here, but if I run into some snags, I may get back to you.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1165774

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice