# Avoiding isNumeric, plus summing dynamic ranges...

#### London_Calling

##### Active Member
This is really dreadful coding, I know. The original idea was to avoid IsNumeric as it unhelpfully doesn't exclude blanks but, actually, my Or/And solution, as well as being hopeless, doesn't do what it's supposed to either.

In addition, as you may see from the later WorksheetFunction, the second idea is to sum a range (assuming there are no cells containing "/" or are blank) - and this is doomed as well, I suspect. Overall, it's a complete dogs breakfast:

Code:
``````If (resultsArr2(i + 1, 66) <> "/" And resultsArr2(i + 1, 66) <> "") Or (resultsArr2(i + 1, 67) <> "/" And resultsArr2(i + 1, 67) <> "") _
Or (resultsArr2(i + 1, 68) <> "/" And resultsArr2(i + 1, 68) <> "") Or (resultsArr2(i + 1, 69) <> "/" And resultsArr2(i + 1, 69) <> "") Then

Ave = Round(WorksheetFunction.Sum(resultsArr2(i + 1, 26), resultsArr2(i + 1, 29), resultsArr2(i + 1, 32), resultsArr2(i + 1, 35), resultsArr2(i + 1, 38), resultsArr2(i + 1, 41), resultsArr2(i + 1, 44), resultsArr2(i + 1, 47), resultsArr2(i + 1, 50), resultsArr2(i + 1, 53)), 2) / counter2``````
It's all just awful. I suppose I'm really just hoping for some guidence and/or tip on more 'elegant' solutions to the IsNumeric and WorksheetFunction issues.

I would very much appreciate any help at all. Thanks.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Not really clear why you wouldn't use IsNumeric in conjunction with Len to check if there is anything in the cell. Also, SUM ignores text and blanks anyway, so not really sure what the point of the tests is anyway?

Nor do I - who is Len, apart from the judge on that dancing show*

*only kidding. Len is probably the answer I thought IsNumeric was. Thanks

Len returns the length of a variable, so:
Code:
``If Len(resultsArr2(i + 1, 66)) > 0 and IsNumeric(resultsArr2(i + 1, 66)) Then``
for example.

That's great, thanks.

The reason for the test was to find the average of cells not containing blanks or /. I understand AVERAGE and SUM ignore blancks but not /. Excluding the / and finding an average has been the issue. But I guess I got lost somewhere....

Sum ignores any text including "/" so I'm not sure what your issue is there.

Replies
14
Views
290
Replies
0
Views
264
Replies
7
Views
285
Replies
0
Views
158
Replies
2
Views
789

1,202,983
Messages
6,052,903
Members
444,610
Latest member
dodong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back