Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel Calculation Help

  • 01-03-2023 01:14PM
    #1
    Gadgetman496
    Registered Users, Registered Users 2 Posts: 8,454 ✭✭✭


    I have the following calculation in an Excel 2007 worksheet and I need to tweak it slightly if anyone can help?

    =IF(SUM(A18)>0,SUM(A18*E18),"")

    The above Calc multiplies cell A18 by Cell E18 and displays the result in cell F18 but only if cell A18 has a value greater than 0 and it works fine, however, I want to change it slightly to stop it placing a zero in cell F18 once a value is put into cell A18

    I want the calc above to work only if the value in both A18 and E18 is greater than zero.

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



Comments

  • #2
    ohnonotgmail
    Registered Users, Registered Users 2 Posts: 40,919 ✭✭✭✭ohnonotgmail


    =IF(AND(SUM(A18)>0,SUM(E18)>0),SUM(A18*E18),"")

    though I would question why you need the sum if it only looks at 1 cell. this does the same thing without the sum

    =IF(AND(A18>0,E18>0),SUM(A18*E18),"")



  • #3
    Gadgetman496
    Registered Users, Registered Users 2 Posts: 8,454 ✭✭✭Gadgetman496


    https://www.boards.ie/discussion/comment/120301075#Comment_120301075

    Thank you so much ohnonotgmail, that works exactly as I wanted it to, really appreciate the help and speedy reply.

    To answer your question, I have no idea why the SUM was there if not needed, I have very little experience with this stuff along with being an old man, my maths are letting me down too I guess :)

    The SUM is removed as per your second example and all working fine.

    Thanks again.

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • #4
    ohnonotgmail
    Registered Users, Registered Users 2 Posts: 40,919 ✭✭✭✭ohnonotgmail


    actually you dont need the other sum either. this is all you need.

    =IF(AND(A18>0,E18>0),A18*E18,"")



  • #5
    Gadgetman496
    Registered Users, Registered Users 2 Posts: 8,454 ✭✭✭Gadgetman496


    https://www.boards.ie/discussion/comment/120301122#Comment_120301122

    I love people who simplify things like that, cheers :)

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • #6
    FelixChan
    Registered Users, Registered Users 2 Posts: 14 ✭FelixChan


    Looks like you need a formula that dynamically updates based on changing values. If you're working with large datasets or complex calculations, structured references in tables can make things easier. You might also explore array formulas or LET to simplify things. I picked up some great tips on structured formulas over at https://excel.tv/. They have some solid explanations that helped me make my sheets more efficient.

    Post edited by FelixChan on


  • Advertisement
Advertisement