Hello,
I'm hoping someone can help me with something peculiar that I can't seem to fix
I'm needing to use the Average formula in VBA as the range that I need to average depends on the variables that are input by the user.
Simply put, this is gi
Is giving a different result to it's worksheet equivalent:
Please Login or Register to view this content.
VBAPlease Login or Register to view this content.
14, 5, 5 = 8.375
Excel
14, 5, 5 = 8
I really would appreciate some help.
Many thanks!
When I do this the VBA code gives me 8. Exact same code.
That range contains 5 cells. You gave 3 values. What values are in all 5 cells? Be specific--cell by cell. Specify any zeroes, specify blanks. Are there formulas in the cells, or values?
It would be better to attach your file.
Hi,
Thanks for looking at this.
That's interesting. Yes, there are formulas in the cells, with some cells showing "FALSE" as there is nothing to vlookup.
Here are the cell values:
Y6 - "11" ---> "=IF(COUNTA(U8),VLOOKUP(U8,Tables!$E$2:$F$17,2,0))"
Y7 - "5" ---> "=IF(COUNTA(U7),VLOOKUP(U7,Tables!$E$2:$F$17,2,0))"
Y8 - "FALSE" ---> "=IF(COUNTA(U8),VLOOKUP(U8,Tables!$E$2:$F$17,2,0))"
Y9 - "FALSE" ---> "=IF(COUNTA(U9),VLOOKUP(U9,Tables!$E$2:$F$17,2,0))"
Y11 - "5" ---> "=IF(COUNTA(U11),VLOOKUP(U11,Tables!$E$2:$F$17,2,0))"
I just tried to run the function with the value 5 in place of the "FALSE" value. So:
Y6 - "11" ---> "=IF(COUNTA(U8),VLOOKUP(U8,Tables!$E$2:$F$17,2,0))"
Y7 - "5" ---> "=IF(COUNTA(U7),VLOOKUP(U7,Tables!$E$2:$F$17,2,0))"
Y8 - "5" ---> "=IF(COUNTA(U8),VLOOKUP(U8,Tables!$E$2:$F$17,2,0))"
Y9 - "5" ---> "=IF(COUNTA(U9),VLOOKUP(U9,Tables!$E$2:$F$17,2,0))"
Y11 - "5" ---> "=IF(COUNTA(U11),VLOOKUP(U11,Tables!$E$2:$F$17,2,0))"
This resulted in:
VBA
= 6.875
Excel
= 6.8
I should also note that when I take away the second range:
It works fine, regardless of the "FALSE" values.Please Login or Register to view this content.
See the link to the uploaded file below.
Last edited by Sc0ut; 03-05-2016 at 11:38 AM.
I've just tried it with static values (i.e. I replace the formula with the actual value e.g. "5") and I get the same problem.
I have reproduced the problem. The attachment button isn't allowing me to attach the file so I've linked the file here.
Note: When you delete the formula in A5, the problem goes away. Very unusual.
It seems that the VBA isn't respecting the break between the first and second range. Instead, it is just including that in it's calculation.
Last edited by Sc0ut; 03-05-2016 at 11:36 AM.
Change into:
PS as for attachments - you may also use Manage Attachments below text edit window in Advanced view, not only paperclip above text edit windowPlease Login or Register to view this content.
Best Regards,
Kaper
Thank you so much, Kaper!
And thanks for the advice about the attachments. Champion.
I need to use this approach with variables (otherwise I wouldn't be using the formula in VBA) and I'm having problems getting it to work. Would anyone be able to help with the syntax?
This executes in the same way that this did:Please Login or Register to view this content.
That is to say that it sees the two ranges as continuous, from Y6:Y11.Please Login or Register to view this content.
As Kaper pointed out, this does work:
What would this look like when using the variables that I've included above?Please Login or Register to view this content.
Last edited by Sc0ut; 03-05-2016 at 08:36 PM.
I've tried this to no avail:
This has the same problem with seeing the range as continuous.Please Login or Register to view this content.
Surely there is a way to do this with variables :/
This formula needs to be used in loops for many cells and rows.
I got it!
I hope that helps someone!Please Login or Register to view this content.
Thanks for the help.
Last edited by Sc0ut; 03-06-2016 at 02:01 AM.
Glad to hear that you managed to solve it. And thanks for the reputation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks