multiple arguments for a if and formula in excel 2010

[Gisher]

hi Folks,

thanks for taking a look at this for me. Here is what I would like help with. I have a column of data that I need to have the following applied to
if the number is less than or equal to 400, then add 20 to it. If the number is greater than 400 and less than or equal to 1499 then apply 5% to it. If the number is greater than or equal to 1500, then apply 75 to it. Then add a value in the adjacent column.

Thanks in advance for your help.

Best,

 

[mumps]

In what column is the data and what value do you want added to the adjacent column?

 

[Gisher]

In what column is the data and what value do you want added to the adjacent column?

In column "I" and I want what ever numer value is in each cell in col "J" added.

So as example ref I2 would be 400 and J2 would be 42 I want the 42 to be added on to whatever number is calculated as a result of the first part of the arugment. I hope I am making sense.
Best,

 

[mumps]

I'm not sure if I understood correctly but try:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomI As Long
    bottomI = Range("I" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("I2:I" & bottomI)
        If rng <= 400 Then
            rng = rng + 20 + Cells(rng.Row, "J")
        ElseIf rng > 400 And rng <= 1499 Then
            rng = rng + (rng * 0.05) + Cells(rng.Row, "J")
        ElseIf rng >= 1500 Then
            rng = rng + 75 + Cells(rng.Row, "J")
        End If
    Next rng
Application.ScreenUpdating = True
End Sub

 

[Gisher]

I'm not sure if I understood correctly but try:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomI As Long
    bottomI = Range("I" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("I2:I" & bottomI)
        If rng <= 400 Then
            rng = rng + 20 + Cells(rng.Row, "J")
        ElseIf rng > 400 And rng <= 1499 Then
            rng = rng + (rng * 0.05) + Cells(rng.Row, "J")
        ElseIf rng >= 1500 Then
            rng = rng + 75 + Cells(rng.Row, "J")
        End If
    Next rng
Application.ScreenUpdating = True
End Sub

Rookie mistake, could I have without code? Id like a formula if possible. My mistake for not mentioning that earlier.

Best,

 

[pjmorris]

Hi, try this:

=if(I2<=400,I2+20,if(I2<=1499,I2*1.05,I2+75))+J2

 

[Gisher]

Thank you Peter. Worked on the first try. I really appreciate your help.

 

[Gisher]

Thanks for your help too Mumps.

Best,

 

[pjmorris]

My pleasure and thanks for the feedback.

Regards

 

[mumps]

@Gisher: I thought that you wanted the result returned in column I. That is why I went with a macro. The formula, which is always the best way to go, would have returned a circular reference error if placed in column I. The macro doesn't have that problem. I'm glad it worked out.