Selecting numeric cells only

[Stoffel24]

Hi all and apologies for the rapidfire queries. I have the following scenario.
Sheet1 has data in columns per component and date

Eg
Cl F Mg
10/10/2002 6.7 9.4 6.1
10/10/2002 7.2 5.76
11/10/2002 3.21 9.3 6.13
etc

I have a subroutine that selects all the data for every day. What I need to do is on sheet2, enter a formula for the average and variance. Initially, what I did was to:
Select all cells for a particular component and day.
Eg cells containing 6.7 and 7.2 (Cl on 10/10/2002)

For Each Cll In Selection.Cells
If ((IsNumeric(Cll.Value)) And (Not (IsEmpty(Cll.Value)))) Then
strLoc = strLoc & ", " & Cll.Address
End If
Next

strLoc = Mid(strLoc, 3) ' & Chr(34)

Range(strLoc).Select

I select strLoc. Then I try to work out the average and variance of these, which gives me a formula something like
=AVERAGE(Sheet1!$B$11,$B$12,$B$13,$B$14)
=VAR(Sheet1!$B$15, B16, B17, $B$18)

There are 2 problems:
Firstly, =AVERAGE(Sheet1!$B$11,$B$12,$B$13,$B$14) is not equal to =AVERAGE(Sheet1!$B$11:$B$14)
The other formula returns an error. I need to use the formula
=VAR(Sheet1!$B$15:$B$18)

Someone please put me out of my misery...
thanks

 

[Zathras]

If you would take the time to read the answers to your earlier posts, you might not have to ask the same questions over and over again.

 

[xlbo]

now now Zathras - hold your horses ;-) I believe Stoffel24 has already apologised for that and mentioned that he posted this before any answers to his previous threads Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

Geoff, you're right and I apologize. But I had just asked this guy a couple of questions for more data (in the other post) and he replied without answering them. You know what that does to me.

 

[bryanbayfield]

Which thread has the method for selecting numeric cells on it? I thought you could do that through the Excel interface (but I've never tried it).

 

[xlbo]

Yeh I know Zathras and I totally sympathise. Guess I've just been brainwashed into customer service standards. the team I work in is totally customer focussed (ie the rest of the company) and we ain't even allowed to raise our voices in the office - even when some dozy ops person has just managed to save a complex spreadsheet as a word doc
ps
Hello Bryan - good to see you r back again Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

Goddammit Geoff, it's good to be back.

 

[Stoffel24]

Hello Zathras and thanks for your replies to my earlier questions. And thanks to the others who have replied as well. I guess I can understand why you are annoyed, but I was attacking a problem and kept getting new ideas/angles which were slightly different to before and so didn't think answers to the other posts would have relevance. But I was wrong there. Nevertheless, I do apologise. I made a mistake. [Cry]

 

[xlbo]

I think that the problem is that the people who may know the answer, also know what kinda info to provide whereas those that are looking for answers, by definition, may not know what is the best info to provide. Guess we have to remember that ALL levels of expertise in excel (and other apps) are welcome here and we should do our best to not get frustrated bu someone else's lack (or percieved lack) of knowledge in a particular area. After all, we were all beginners at some stage. I know, if I post in an Access forum, I often don't provide enough info 'cos I'm not so familiar with Access. If you don't know what the problem is, it's harder to provide the info that'll solve it.
Everyone be happy Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SBendBuckeye]

Hello Stoffel24,

Look up the SpecialCells method. It allows you to select only certain types of data, including numeric for a given range object.

If you do a search or advanced search on this board for SpecialCells you'll find several examples of how to set it up.

Hope this helps and good luck!