Results 1 to 8 of 8

Thread: Multiple cell criteria

  1. #1

    Multiple cell criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi I am a very new excel user and I want to know if I can formulate the following so that in one colum when I type in the cost it will automatically calculate and display formulated amount once typed in.
    The following is the criteria
    If cost is <50 then x cost by 2 +10 +10%(gst)
    If cost is 51-80 then x cost by 2 +10
    If cost is 81-120 then x cost by 2
    if cost is >120 then cost +100 + 10%(gst)
    Can this be achieved or will I need to do 4 colums, one for each formula......
    I also need help in setting these individual fomulae up if that is the case.

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by nancyb View Post
    Can this be achieved or will I need to do 4 colums, one for each formula..., I also need help in setting these individual fomulae up if that is the case.
    You are unclear, but try
    Code:
    =IF(A1<50;"result1";"")
    Code:
    =IF(AND(A1>=50;A1<=80);"result2";"")
    Code:
    =IF(AND(A1>=81;A1<=120);"result3";"")
    Code:
    =IF(A1>120;"result4";"")
    This is nested formula
    Code:
    =IF(A1<50;"result1";IF(AND(A1>=50;A1<=80);"result2";IF(AND(A1>=81;A1<=120);"result3";IF(A1>120;"result4";""))))
    This is the formula to 10% from value in A1
    Code:
    =A1*10/100
    Last edited by navic; 2017-01-17 at 08:55 AM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Thankyou for your help I have finally worked it out however I want to know if you can add the round up function to the result so that all answers go up to the next whole number for eg:

    Using the following formula =IF(AND(B7>=50.01,B7<=100),(((B7*2)+10)*1.1),"") the answer is $144.98 and displays as such, I want it to display $145.00 can I add something to the formula to do this.

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,680
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Do you want it to display only (formatting) or does the underlying value have to be rounded?
    In the latter case should it always roundup ( see the ROUNDUP function) or rounddown ( see the ROUNDDOWN function)
    https://support.office.com/en-us/art...1-B3E7F61A213C
    Thank you Ken for this secure forum.

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by nancyb View Post
    answer is $144.98 and displays as such, I want it to display $145.00
    Use
    Code:
    =ROUNDUP(yourformula;0)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    so like this =ROUNDUP(=IF(AND(B7>=50.01,B7<=100),(((B7*2)+10)*1.1),"");0) ? This did not work and nor did this =ROUNDUP(IF(AND(B7>=50.01,B7<=100),(((B7*2)+10)*1.1),"");0) ??

  7. #7
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,680
    Articles
    0
    Excel Version
    2010 on Xubuntu
    You'll have to replace the semi colon before the 0 with a comma ( navic uses other regional settings as stated in his sig)

    =ROUNDUP(IF(AND(B7>=50.01,B7<=100),(((B7*2)+10)*1.1),""),0) ??

    BTW did you read post #4 ?
    Thank you Ken for this secure forum.

  8. #8
    brilliant - thankyou

Posting Permissions

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