| 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. |
Multiple cell criteria
- Thread starter nancyb
- Start date
navic
New member
- Joined
- Aug 27, 2013
- Messages
- 901
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 2013
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";"")
Code:
=IF(A1<50;"result1";IF(AND(A1>=50;A1<=80);"result2";IF(AND(A1>=81;A1<=120);"result3";IF(A1>120;"result4";""))))
Code:
=A1*10/100
Last edited:
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.
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.
Pecoflyer
Admin Alumnus
- Joined
- Oct 13, 2011
- Messages
- 1,803
- Reaction score
- 1
- Points
- 38
- Location
- Brussels Belgium
- Excel Version(s)
- 2010 on Xubuntu - O365
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/article/ROUND-function-C018C5D8-40FB-4053-90B1-B3E7F61A213C
In the latter case should it always roundup ( see the ROUNDUP function) or rounddown ( see the ROUNDDOWN function)
https://support.office.com/en-us/article/ROUND-function-C018C5D8-40FB-4053-90B1-B3E7F61A213C
navic
New member
- Joined
- Aug 27, 2013
- Messages
- 901
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 2013
Pecoflyer
Admin Alumnus
- Joined
- Oct 13, 2011
- Messages
- 1,803
- Reaction score
- 1
- Points
- 38
- Location
- Brussels Belgium
- Excel Version(s)
- 2010 on Xubuntu - O365
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 ?
=ROUNDUP(IF(AND(B7>=50.01,B7<=100),(((B7*2)+10)*1.1),""),0) ??
BTW did you read post #4 ?
