In excel how can I tell if cell is a number or formula 3

[ls62]

Hi,

I want to test a range of cells and sum all the cells that have a number in them, but not any cells that have formulas. For example I have a range of rows/columns with numbers and then subtotals for a month, more numbers and another subtotal

ex:
1:5
2:6
3:3
4:14 <subtotal formula
5:4
6:1
7:3
8:8 <subtotal formula

I want count rows with numbers (not formulas) and also sum. I would like to do this as a function or formula, not a macro.

Thanks for any help

LEE

 

[Wray69]

You can do an array formula of =SUM(IF(ISNUMBER(A1:A11),1,0)) hit CTRL+SHIFT+ENTER at the end to make it an array...
This should work...

Regards -

Wray

 

[Dreamboat]

Why are you inserting your own subtotals? Have you tried the Data-Subtotals feature, which will probably do all of this for you?

Just a guess... Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Zathras]

Just use the SUBTOTAL function again, this time with first parameter = 2.

B2: 1.5
B3: 2.6
B4: 3.3
B5: =SUBTOTAL(9,B2:B4)  <-- 7.4
B6: 5.4
B7: 6.1
B8: 7.3
B9: =SUBTOTAL(9,B6:B8)  <-- 18.8
B10: =SUBTOTAL(2,B2:B9) <-- 6
B11: =SUBTOTAL(9,B2:B9) <-- 26.2

SUBTOTAL does not process cells with SUBTOTAL formula in them.

 

[Dreamboat]

Cool, Zathras. You have surprised me MANY times. Keep up the GREAT help here.... Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[ls62]

Wray69,

Sorry, I tried what you suggested, but the problem is that if I have 14 cells and 2 have formulas in them it still includes them up because the formula results in a number (I think).

Any other suggestions.

THANKS.
Lee

 

[ls62]

Zathras,

I like you idea, but I'll give you my exact situation, I was trying to be generic, but I guess that won't work.

I have a spreadsheet with 4 wks data then a 'average' function for those 4 wks... then another 4 wks and another 'average' function for those 4 wks... etc for each month in the year. At the end I'm trying to average the whole year in one cell, and total the range in another by selecting the range a1:a? which includes these average cells.

How can I do:
1) average a1:a20 skipping the cells that have the average formula in them.

or

2) sum and count the number of cells in the range that have numbers (not blank, zero ok) but not formulas?

Thanks again for your time and help.

LEE

 

[Zathras]

Don't use AVERAGE(a1:a20), use SUBTOTAL(1,a1:a20) then later you can use SUBTOTAL(1,a1:a5000) to get the over-all average.

You can easily use edit/replace to replace the string

  =AVERAGE(

with the string

  =SUBTOTAL(1,

First select the sheet (Ctrl-A) and then press Shift-F5 and click Replace...

 

[ls62]

Zathras,

Excellent! I didn't realize that the subtotal function could do all those things (count, avg, etc).

Thanks again!

LEE

 

[bandit600]

I like to look at things from alternative perspectives. (Its just the way I am) Sometimes this works and sometimes I am way off the mark, as some of you may have noticed.

There are always posts in this forum where people are trying to apply numeric functions on what turns out to be text. So why not convert the subtotals to text.

Am I right in thinking that the subtotals would then be ignored in the final average, count and sum calculations?

bandit600

 

[ls62]

Ok. That worked great. Now I have another twist. What if I want to count (using subtotal or something) only the cells in the range that are non-zero?

Can this be done?

Thanks

Lee

 

[Zathras]

I don't know of a direct way to do that.

There are 2 possibilities:

(1) Use another column with the formula

        =IF(B2=0,"",B2)

only on the rows where you want to count.
I.e., leave the subtotal rows blank in that column.
Then use the COUNT( ) function.

(2) Write a custom function in VBA.

 

[slreynolds]

I believe you can do this with the COUNTIF function:

COUNTIF(B3:B36,&quot;<>0&quot

 

[Dreamboat]

To get an average in one formula:

=SUM(A1:A10)/COUNTIF(A1:A10,&quot;<>0&quot

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Zathras]

The problem with both of the above is that COUNTIF would include non-zero SUBTOTALs in the count.

 

[ls62]

Zathras,

I agree, the problem IS that the two options given above would include the subtotals. I really didn't want to have to do this with a vba macro, but it looks like I have no other choice. I can do it... since I already have a macro loading the sheets with the data for each week. Just thought it could be setup as a static function and would be easier.

Thanks to all for your help and time.

LEE

 

[Zathras]

The way I would do it would be to create a User-Defined function. Then use it as a formula in the worksheet just like any other.

The code could look like this:

Function CountSpecial(rng As Range) As Long
Dim c As Range
  For Each c In rng
    If Not c.HasFormula Then
      If c.Value & 0 <> 0 Then
        CountSpecial = CountSpecial + 1
      End If
    End If
  Next c
End Function

and you would use it like this:

  =CountSpecial(A1:A6000)

just like it was one of the built-in Excel functions.

The function above is sufficient the way it is if the only formulas in the column are the averages, subtotals, etc., and all of the values are keyed in. If your values can be formulas, then more code is needed to exclude only the formulas you dont want.

 

[ls62]

Zathras,

Your great! I can't thank you enough for saving me hours of work trying to figure this out on my own. I may have figured it out eventually, but its nice to have a forum like this to turn to!

I'll give your last post a try, but it looks like it will do what I want.

LEE

 

[bandit600]

Going back to the idea of using Text for the subtotals, you may be able to avoid VBA.

In case there was any doubt, I didn’t mean formatting them as Text, as has been said in this forum many times, formatting doesn’t change what’s underneath.

I’ve tried the following on some sample data and it seems to work

Calculate the Subtotals with something like

=TEXT(SUBTOTAL(1,A10:A20), “0.00”)

Then you could do your count of non zeroes with

=COUNT(A1:A20)-COUNTIF(A1:A20,&quot;=0&quot

If you need to use the subtotals for anything else, just convert them back.

Am I way off the mark again or would this work?

bandit600

 

[ls62]

Bandit600,

How does this exclude the subtotal cells that are not zero within the range from being included in count (a1:a20)?

LEE