Use VBA to compute mathematics formula

[mpham168]

So I've got a formula computing some values...

basically if
cell column J has <1.5 Million then 5% x column J
cell column J has >=1.5 Million but <3 Million then 10% x column J
cell column J has >=3 Million but <10 Million then 15% x column J
cell column J has >=10 Million then 20% x column J

I know I can use the below formula... and fill the column BUT I would rather have it be computed and just a value returned.

=IF(J2>=10000000,ROUNDDOWN(J2*0.2,1),IF(AND(J2>=3000000,J2<10000000),ROUNDDOWN(J2*0.15,1),IF(AND(J2>=1500000,J2<3000000),ROUNDDOWN(J2*0.1,1),IF(J2<1500000,ROUNDDOWN(J2*0.05,1)))))
Thanks

mpham

 

[Damon Ostrander]

Hi mpham,

I don't know of a formula to do this that doesn't require any IF functions, but here is a simpler formula than the one you have:

=IF(J2<1500000,0.05,IF(J2<3000000,0.1,IF(J2<10000000,0.15,0.2)))*J2

Damon

 

[Michael M]

So which column do you want the result in ??

 

[Michael M]

I have to duck out for a while, so I've taken a guess that the formula goes in column "K", if that's incorrect, change the column reference in line 4

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "J").End(xlUp).Row
    With Range("K1:K" & lr) 'change col reference to suit
        .Formula = "=IF(J2<1500000,0.05,IF(J2<3000000,0.1,IF(J2<10000000,0.15,0.2)))*J2"
        .Value = .Value
    End With
End Sub