CODEWITHSUNDEEP
vbaexcel
walter while
walter while

Reputation: 107

percentile formula error in VBA

I'm trying to apply a formula through VBA for a particular range. This is the code in my VBA editor:

Sheets("WBR45").Range("AE105").Formula = "=PERCENTILE.INC(TP!$A$3:$A$30:$B$3:$B$30:$C$3:$C$30:$E$3:$E$30,50%)*24"

And the below formula gets updated in the destination cell when this is run:

=PERCENTILE.INC(TP!$A$3:$A$30:$B$3:$B$30:$C$3:$C$30:$E$3:$E$30,50%)*24

But I get an error in the destination cell as #VALUE!.

And when I click on "Show Calculation steps", only this part of the formula is underlined :

TP!$A$3:$A$30:$B$3:$B$30

I have no idea what is wrong with this simple formula. Can someone please take a look

Upvotes: 0

Views: 511

Answers (2)

YowE3K
YowE3K

Reputation: 23974

You appear to have three errors in your formula:

  1. You are using : to separate ranges instead of ,
  2. You are not specifying which sheet the second, third and fourth ranges refer to, therefore it is defaulting to the sheet on which the formula occurs (i.e. sheet "WBR45")
  3. Multiple ranges will need to be enclosed within brackets (...) in order to be passed as a single range.

If you are trying to have your function operate on the range A3:C30 together with the range E3:E30 (i.e. A3:E30 but ignoring column D), with those ranges being on the "TP" worksheet, I believe that you need to change your formula to

Sheets("WBR45").Range("AE105").Formula = "=PERCENTILE.INC((TP!$A$3:$A$30,TP!$B$3:$B$30,TP!$C$3:$C$30,TP!$E$3:$E$30),50%)*24"

or, slightly simplified

Sheets("WBR45").Range("AE105").Formula = "=PERCENTILE.INC((TP!$A$3:$C$30,TP!$E$3:$E$30),50%)*24"

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

Honestly I have no clue about what you're doing with this, but this may fix it:

"=PERCENTILE.INC(TP!$A$3:$A$30:TP!$B$3:$B$30:TP!$C$3:$C$30:TP!$E$3:$E$30,50%)*24"

Upvotes: 1

Related Questions