Results 1 to 9 of 9

If formula please

This is a discussion on If formula please within the Excel Questions forums, part of the Question Forums category; Can I please have the formula for: if a range is less than a number (in a different cell) then ...

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    4

    Default If formula please

    Can I please have the formula for: if a range is less than a number (in a different cell) then 0. If the range is greater than the number then take the amount greater than the number.
    Thank you

  2. #2
    New Member
    Join Date
    May 2012
    Posts
    8

    Default Re: If formula please

    Quote Originally Posted by josh613 View Post
    Can I please have the formula for: if a range is less than a number (in a different cell) then 0. If the range is greater than the number then take the amount greater than the number.
    Thank you
    If a cell is less than the number in that different cell, or if all items in the range are smaller?

    would something like the below work? Start your list at A2; $D$1 is your cell with the value you are testing for.

    =if(A2<$D$1,0,if(A2>$D$1,A2,if(A2=$D$1,{true condition},"ERROR")))

    or

    =if(A2<=$D$1,0,if(A2>$D$1,A2,"error"))

  3. #3
    Board Regular mmodroger's Avatar
    Join Date
    Apr 2012
    Location
    Thailand
    Posts
    223

    Default Re: If formula please

    Sheet1
    C
    5 =IF(range<number,0,IF(range>number,answer,""))
    Excel 2007

    I love excel formulas but i am a VBA dummy @_@
    Excel 2007 Window XP
    Excel tables to the web >> Excel Jeanie HTML 4

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    33,201

    Default Re: If formula please

    Welcome to the board..

    So to be specific with the ranges..

    A1 and B1
    If A1 is less than B1, 0
    If A1 is greater than B1, A1

    What if the 2 cells are the same?
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    New Member
    Join Date
    May 2012
    Posts
    4

    Default Re: If formula please

    Thank you all for the warm welcome and trying to lend me a hand!
    Let me try to be more specific.
    A1 has an amount of $100. If B1 through G1 add up to $90 then G2 should have a value of $0. If B1 through H1 add up to $110 then H2 should have a value of $10.
    I greatly appreciate your time!

  6. #6
    Board Regular mmodroger's Avatar
    Join Date
    Apr 2012
    Location
    Thailand
    Posts
    223

    Default Re: If formula please

    Sheet1
    C
    15 =IF(sum($B$1:G1)<=100,0,IF(sum($B$1:G1)>100,sum($B$1:G1)-100,""))
    Excel 2007


    copy in G2 and drag across
    Last edited by mmodroger; May 3rd, 2012 at 12:24 AM.
    I love excel formulas but i am a VBA dummy @_@
    Excel 2007 Window XP
    Excel tables to the web >> Excel Jeanie HTML 4

  7. #7
    Board Regular
    Join Date
    Mar 2012
    Location
    Singapore
    Posts
    77

    Default Re: If formula please

    Is this what you're looking for?

    Formula in G2: =IF(SUM(B1:G1)=A1-10,0,"false value")

    Formula in H2: =IF(SUM(B1:H1)=A1+10,0,"false value")

    Clear data helps everyone - the person requesting and the person replying.

    Use something like Excel Jeanie or MrExcel HTML Maker to post data that looks like its on a spreadsheet

  8. #8
    New Member
    Join Date
    May 2012
    Posts
    4

    Default Re: If formula please

    Quote Originally Posted by mmodroger View Post
    Sheet1
    C
    15 =IF(sum($B$1:G1)<=100,0,IF(sum($B$1:G1)>100,sum($B$1:G1)-100,""))
    Excel 2007


    copy in G2 and drag across
    Thank you! It works great and I am very appreciative!!
    Have a good night!

  9. #9
    New Member
    Join Date
    May 2012
    Posts
    4

    Default Re: If formula please

    Thank you also to all who responded!!
    It is a great feeling to know that there are people out there who can help when you are stuck!
    Have a great night!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com