Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function
I would like one of four discount percentages to appear on a spreadsheet,
depending on what the dollar amount is in A1... If A1 is equal to or greater than $600 and less than or equal to $1,500 the discount is 5% If A1 is equal to or greater than $1,501 and less than or equal to $3,000 the discount is 10% If A1 is equal to or greater than $3,001 and less than or equal to $6,000 the discount is 15% If A1 is equal to or greater than $6,001 and less than or equal to $15,000 the discount is 20% Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function
=IF(AND(A1=600,A1<=1500),5%,IF(AND(A1=1501,A1<=3 000),10%,IF(AND(A1=3001,A1<=6000),15%,IF(AND(A1= 6001,A1<=15000),20%))))
is the answer to your question, but this MAY produce a result of FALSE because A1 may be <600 or A1 may be 15000 and you didn't account for these cases. HTH "joaniemic" wrote: I would like one of four discount percentages to appear on a spreadsheet, depending on what the dollar amount is in A1... If A1 is equal to or greater than $600 and less than or equal to $1,500 the discount is 5% If A1 is equal to or greater than $1,501 and less than or equal to $3,000 the discount is 10% If A1 is equal to or greater than $3,001 and less than or equal to $6,000 the discount is 15% If A1 is equal to or greater than $6,001 and less than or equal to $15,000 the discount is 20% Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function
Bob,
A1 cannot be less than $600 because we would not prepare this if it were. However, for the last one I should have said if A1 is equal to or greater than $6,001 the discount percentage is 20% and just left it at that because anything greater than $6,000 is at 20%. Thanks. "Bob Umlas, Excel MVP" wrote: =IF(AND(A1=600,A1<=1500),5%,IF(AND(A1=1501,A1<=3 000),10%,IF(AND(A1=3001,A1<=6000),15%,IF(AND(A1= 6001,A1<=15000),20%)))) is the answer to your question, but this MAY produce a result of FALSE because A1 may be <600 or A1 may be 15000 and you didn't account for these cases. HTH "joaniemic" wrote: I would like one of four discount percentages to appear on a spreadsheet, depending on what the dollar amount is in A1... If A1 is equal to or greater than $600 and less than or equal to $1,500 the discount is 5% If A1 is equal to or greater than $1,501 and less than or equal to $3,000 the discount is 10% If A1 is equal to or greater than $3,001 and less than or equal to $6,000 the discount is 15% If A1 is equal to or greater than $6,001 and less than or equal to $15,000 the discount is 20% Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function
Nor does the question allow for values between 1500 and 1501, nor between
3000 and 3001, nor between 6000 and 6001. If the OP actually intended not to say If A1 is equal to or greater than $1,501 and less than or equal to $3,000 the discount is 10% but If A1 is greater than $1,500 and less than or equal to $3,000 the discount is 10% and so on, then the formula can be simplified to =IF(AND(A1=600,A1<=1500),5%,IF(A1<=3000,10%,IF(A1 <=6000,15%,IF(A1<=15000,20%)))) -- David Biddulph "Bob Umlas, Excel MVP" wrote in message ... =IF(AND(A1=600,A1<=1500),5%,IF(AND(A1=1501,A1<=3 000),10%,IF(AND(A1=3001,A1<=6000),15%,IF(AND(A1= 6001,A1<=15000),20%)))) is the answer to your question, but this MAY produce a result of FALSE because A1 may be <600 or A1 may be 15000 and you didn't account for these cases. HTH "joaniemic" wrote: I would like one of four discount percentages to appear on a spreadsheet, depending on what the dollar amount is in A1... If A1 is equal to or greater than $600 and less than or equal to $1,500 the discount is 5% If A1 is equal to or greater than $1,501 and less than or equal to $3,000 the discount is 10% If A1 is equal to or greater than $3,001 and less than or equal to $6,000 the discount is 15% If A1 is equal to or greater than $6,001 and less than or equal to $15,000 the discount is 20% Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |