Simple IF() formula

[GoBruins]

hi,

let's say i have a number in cell A1

i need a formula that figures out the following:

if A1 is less than or equal to 40,000, then the result should be $0
if A1 is between 40,000 and 60,000, then the result should be $75
if A1 is greater than 60,000, then the result is $75 plus $5 for each
1,000 above 60,000

i can input a simle IF() formula like =IF(A1<=40000,0,75), but i can't
account for the third condition.

thanks in advance.

 

[Niek Otten]

You can find a solution here:

http://www.mcgimpsey.com/excel/variablerate.html

Another way is to use my UDF. In case you don't know (yet) how to use UDFs I include instructions as well

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
' =========================================================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Niek Otten, March 31, 2006

' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' =========================================================

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


| hi,
|
| let's say i have a number in cell A1
|
| i need a formula that figures out the following:
|
| if A1 is less than or equal to 40,000, then the result should be $0
| if A1 is between 40,000 and 60,000, then the result should be $75
| if A1 is greater than 60,000, then the result is $75 plus $5 for each
| 1,000 above 60,000
|
| i can input a simle IF() formula like =IF(A1<=40000,0,75), but i can't
| account for the third condition.
|
| thanks in advance.
|

 

[Gary''s Student]

You don't need any if statements:

= (A1>40000) * (A1<60000)*75+(A1>60000) *(75+5*(A1-60000)/1000)

 

[CLR]

=IF(A1>60000,75+(((A1-60000)/1000)*5),IF(A1>=40000,75,0))

Vaya con Dios,
Chuck, CABGx3

 

[joeu2004]

i need a formula that figures out the following:
if A1 is less than or equal to 40,000, then the result should be $0
if A1 is between 40,000 and 60,000, then the result should be $75
if A1 is greater than 60,000, then the result is $75 plus $5 for each
1,000 above 60,000

The KISS solution, which follows you initial attempt, is:

=if(A1<=40000, 0, 75 + 5*int(max(0,A1-60000)/1000))

Some discussion of the int(...) expression is needed.

I interpret you to mean that you want an additional $0 for any amount
over 60000 but less than 61000, $5 for any amount at least 61000 but
less than 62000, $10 for any amount at least 62000 but less than 63000,
etc.

If you want an additional $5 for any amount over 60000 but less than
61000, $10 for any amount at least 61000 but less than 62000, etc, then
try:

=if( A1<=40000, 0, 75 + 5*roundup(max(0,A1-60000)/1000,0) ) )

If you want something else, please be more specific.

 

[GoBruins]

The KISS solution, which follows you initial attempt, is:

=if(A1<=40000, 0, 75 + 5*int(max(0,A1-60000)/1000))

Some discussion of the int(...) expression is needed.

I interpret you to mean that you want an additional $0 for any amount
over 60000 but less than 61000, $5 for any amount at least 61000 but
less than 62000, $10 for any amount at least 62000 but less than 63000,
etc.

If you want an additional $5 for any amount over 60000 but less than
61000, $10 for any amount at least 61000 but less than 62000, etc, then
try:

=if( A1<=40000, 0, 75 + 5*roundup(max(0,A1-60000)/1000,0) ) )

If you want something else, please be more specific.

hi,

your first assumption was correct.

i'm very grateful for all the help. i've seen solutions from KISS to
the sublime - awesome.