1. ## Multiple cell criteria

 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. Originally Posted by nancyb
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`

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. 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

5. Originally Posted by nancyb
answer is \$144.98 and displays as such, I want it to display \$145.00
Use
Code:
`=ROUNDUP(yourformula;0)`

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. 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 ?

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
•