power function in VBA

[prometheus]

Hi

i have this code


Cells(21 + i, 39).Value = -(10 ^ (Abs(Cells(21 + i, 37) / 3 - 1)))

which is incorrectly calculating this excel function : -(10^(ABS(AK2)/3)-1)

i think i should use Application.WorksheetFunction.Power but i cant get it to work....
nota:

i was able to use change the forumla to worksheet function : Cells(10 + i, 41).Value = -Application.WorksheetFunction.Power(Abs(Cells(10 + i, 37) / 3), 10) - 1

or the other one Cells(10 + i, 41).Value = -(10 ^ (Abs(Cells(10 + i, 37)) / 3) - 1)

Both formula's "work" , ie dont give an error but both calculate the wrong number

example: value 0.0629 in excel with this formula (=(10^(ABS(AK16)/3)-1) ) returns value = 0.0495

if i run the VBA formulas i get : 9.528

[Paul_Hossler]

For


-(10^(ABS(AK2)/3)-1)




-(10 ^ (Abs(Cells(21 + i, 37) / 3 - 1)))

should be

-(10 ^ (Abs(Cells(21 + i, 37)) / 3) - 1)


(I think)

[prometheus]

i was able to use change the forumla to worksheet function : Cells(10 + i, 41).Value = -Application.WorksheetFunction.Power(Abs(Cells(10 + i, 37) / 3), 10) - 1

or the other one Cells(10 + i, 41).Value = -(10 ^ (Abs(Cells(10 + i, 37)) / 3) - 1)

Both formula's "work" , ie dont give an error but both calculate the wrong number

example: value 0.0629 in excel with this formula (=(10^(ABS(AK16)/3)-1) ) returns value = 0.0495

if i run the VBA formulas i get : 9.528

[Paul_Hossler]

Your fomulas are still inconsistant

1. in

Cells(10 + i, 41).Value = -Application.WorksheetFunction.Power(Abs(Cells(10 + i, 37) / 3), 10) - 1

you raise it to a power and then subtract 1


2. You have the arguments to POWER() reversed

MsgBox -1 * Application.WorksheetFunction.Power(10, Abs(n) / 3) - 1


3. In

Cells(10 + i, 41).Value = -(10 ^ (Abs(Cells(10 + i, 37)) / 3) - 1)

You subtract 1 and then raise 10 to that number


In VBA, I'd suggest #3 with the correct formula

[snb]

Did you ever consider to upload a sample file ?

P.s. are you familiar wiht helpmij.nl ? (a lot of your contryfolks are)

[mikerickson]

The parenthesis in the OP are in different places

-(10 ^ (Abs(Cells(21 + i, 37) / 3 - 1))) : vba

-(10^(ABS(AK2)/3)-1) ; worksheet


the -1 is inside the ABS in the vba version, outside of it in the worksheet version.

[prometheus]

hi all thanks for the help.. its in deed to put the parenthesis in the right place.. :)