Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: excel formula

  1. #1
    Join Date
    Apr 2002
    Location
    Godwin Beach 4511
    Posts
    20,454
    Total Downloaded
    0

    excel formula

    Hi

    i am wanting to automate calculations in an excel spreadsheet but i am not getting it right

    i wish to have a formula so that if cell D14 has a value greater than 0 then use this formula to put a value in this cell

    what i have is

    =IF(D14>=0,((D14*10.75)+5.5))

    and it always show 5.50 if the value is 0

    what am i doing wrong....

    please

    1998 Discovery 300TDi Manual SE7
    1996 Discovery 300TDi Auto
    2012 SZ Territory TX 2.7TDCi

    "Make the lie big, make it simple, keep saying it, and eventually they will believe it." -- a warning from Adolf Hitler
    "If you don't have a sense of humour, you probably don't have any sense at all!" -- a wise observation by someone else
    'If everyone colludes in believing that war is the norm, nobody will recognize the imperative of peace." -- Anne Deveson
    “What you leave behind is not what is engraved in stone monuments, but what is woven into the lives of others.” - Pericles
    "The happiness of your life depends upon the quality of your thoughts." Marcus Aurelius

  2. #2
    Join Date
    Apr 2011
    Location
    Wandong,Victoria,Australia
    Posts
    3,276
    Total Downloaded
    0
    Hi Inc,

    I've read your post a few times - and I'm still not sure I understand your question!

    Are you saying that if the value is 0 - then you don't want it to display anything at all?

    If you change the >=0 to just >0 then if the value is zero is says "False"...

    I think that I'm missing something.. Sorry.
    54 Series 1 86
    61 Series 2 109 - Club Rego
    76 2 Door Range Rover
    78 101 Forward Control - Club Rego
    88 Perentie FFR - Club Rego
    90 4 Door Range Rover - Club Rego
    93 Discovery 1 200 Tdi - Club Rego
    98 Freelander 1 - Full Rego
    22 Defedner 90 - Full rego

  3. #3
    Join Date
    Apr 2002
    Location
    Godwin Beach 4511
    Posts
    20,454
    Total Downloaded
    0

    excel formula

    That's correct.. if cell value is 0 then don't bother with formula...

    If cell value is bigger than 0 apply formula
    1998 Discovery 300TDi Manual SE7
    1996 Discovery 300TDi Auto
    2012 SZ Territory TX 2.7TDCi

    "Make the lie big, make it simple, keep saying it, and eventually they will believe it." -- a warning from Adolf Hitler
    "If you don't have a sense of humour, you probably don't have any sense at all!" -- a wise observation by someone else
    'If everyone colludes in believing that war is the norm, nobody will recognize the imperative of peace." -- Anne Deveson
    “What you leave behind is not what is engraved in stone monuments, but what is woven into the lives of others.” - Pericles
    "The happiness of your life depends upon the quality of your thoughts." Marcus Aurelius

  4. #4
    Join Date
    Apr 2011
    Location
    Wandong,Victoria,Australia
    Posts
    3,276
    Total Downloaded
    0
    Have you tried using the IF NOT expression?

    In English - if NOT D14 =0 then do calc??
    54 Series 1 86
    61 Series 2 109 - Club Rego
    76 2 Door Range Rover
    78 101 Forward Control - Club Rego
    88 Perentie FFR - Club Rego
    90 4 Door Range Rover - Club Rego
    93 Discovery 1 200 Tdi - Club Rego
    98 Freelander 1 - Full Rego
    22 Defedner 90 - Full rego

  5. #5
    Join Date
    Apr 2011
    Location
    Wandong,Victoria,Australia
    Posts
    3,276
    Total Downloaded
    0
    =IF(NOT(D14=0),(D14*10.75)+5.5,"")

    Using the above doesn't display the word "False" - but I don't think that's answering your question either?!?
    54 Series 1 86
    61 Series 2 109 - Club Rego
    76 2 Door Range Rover
    78 101 Forward Control - Club Rego
    88 Perentie FFR - Club Rego
    90 4 Door Range Rover - Club Rego
    93 Discovery 1 200 Tdi - Club Rego
    98 Freelander 1 - Full Rego
    22 Defedner 90 - Full rego

  6. #6
    Join Date
    Nov 2016
    Location
    Eltham, Victoria
    Posts
    181
    Total Downloaded
    0
    =IF(D14>=0,((D14*10.75)+5.5))


    The problem
    incisor is if D14 = 0 then it meets if 0>=0 so it will calculate (0*10.75) + 5.5 which = 5.5

    With not knowing what you are trying to achieve I assume you want to use >. Instead of >=



    Hope this helps

  7. #7
    Join Date
    Feb 2007
    Location
    Perth
    Posts
    3,807
    Total Downloaded
    0
    Quote Originally Posted by incisor View Post
    That's correct.. if cell value is 0 then don't bother with formula...

    If cell value is bigger than 0 apply formula
    =IF(D14>0,((D14*10.75)+5.5),"")
    2011 D4 3.0 SDV6
    1999 D2 V8, in heaven
    1984 RRC, in hell

  8. #8
    p38arover's Avatar
    p38arover is offline Major part of the heart and soul of AULRO.com
    Administrator
    I'm here to help you!
    Gold Subscriber
    Join Date
    Jan 1970
    Location
    Western Sydney
    Posts
    30,160
    Total Downloaded
    0
    Quote Originally Posted by incisor View Post
    what i have is

    =IF(D14>=0,((D14*10.75)+5.5))

    and it always show 5.50 if the value is 0
    Two things.

    Firstly, it's looking to see if D14 is greater than or equal to zero so it will always be true unless D14 is less than zero.

    Secondly, there is a missing argument to the logical test: IF(logical_test,[value_if_true],[value_if_false])

    Try =IF(D14>0,((D14*10.75)+5.5),0)

    Note the change of >= to just > and the ,0 at the end.
    Ron B.
    VK2OTC

    2003 L322 Range Rover Vogue 4.4 V8 Auto
    2007 Yamaha XJR1300
    Previous: 1983, 1986 RRC; 1995, 1996 P38A; 1995 Disco1; 1984 V8 County 110; Series IIA



    RIP Bucko - Riding on Forever

  9. #9
    Join Date
    Apr 2002
    Location
    Godwin Beach 4511
    Posts
    20,454
    Total Downloaded
    0
    Quote Originally Posted by p38arover View Post
    Note the change of >= to just > and the ,0 at the end.
    thanks Ron that did it exactly!

    thanks for the help people, was much appreciated!
    1998 Discovery 300TDi Manual SE7
    1996 Discovery 300TDi Auto
    2012 SZ Territory TX 2.7TDCi

    "Make the lie big, make it simple, keep saying it, and eventually they will believe it." -- a warning from Adolf Hitler
    "If you don't have a sense of humour, you probably don't have any sense at all!" -- a wise observation by someone else
    'If everyone colludes in believing that war is the norm, nobody will recognize the imperative of peace." -- Anne Deveson
    “What you leave behind is not what is engraved in stone monuments, but what is woven into the lives of others.” - Pericles
    "The happiness of your life depends upon the quality of your thoughts." Marcus Aurelius

  10. #10
    Join Date
    Apr 2002
    Location
    Godwin Beach 4511
    Posts
    20,454
    Total Downloaded
    0
    Quote Originally Posted by Ferret View Post
    =IF(D14>0,((D14*10.75)+5.5),"")
    this left the cell empty, negating my formatting as currency..

    will come in handy elsewhere!

    thank you...
    1998 Discovery 300TDi Manual SE7
    1996 Discovery 300TDi Auto
    2012 SZ Territory TX 2.7TDCi

    "Make the lie big, make it simple, keep saying it, and eventually they will believe it." -- a warning from Adolf Hitler
    "If you don't have a sense of humour, you probably don't have any sense at all!" -- a wise observation by someone else
    'If everyone colludes in believing that war is the norm, nobody will recognize the imperative of peace." -- Anne Deveson
    “What you leave behind is not what is engraved in stone monuments, but what is woven into the lives of others.” - Pericles
    "The happiness of your life depends upon the quality of your thoughts." Marcus Aurelius

Page 1 of 3 123 LastLast

Tags for this Thread

Bookmarks

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 AULRO.com ONLY!
Search All the Web!