Cell format to use for calculation

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I have multiple spreadsheets in a workbook and each worksheet contains
recorded rainfall data for each day of a month. In the column containing the
recorded rainfall, the data is entered as:

1

or

1"

I need to add the totals for each month, but I cannot since some of the
contains contain alphanumeric values.is there a function that I can use that
can disregard the text and focus only on the numeric value of the cell
instead of having to copy the value into a new cell each time I want to add?

Thanks.
 
Try this small UDF:

Public Function zum(r As Range) As Variant
zum = 0
For Each rr In r
zum = zum + Val(rr.Value)
Next
End Function


You can use it in the worksheet like SUM(). It will ignore trailing text in
cells that start with a number, but end with text.
 
both suggestions worked wonderfully and thanks for your help. can you explain
the "--" in functions? To me, it logically seems that is a double-negative,
but I am sure there is a different meaning.
Thanks!
 
It converts TRUE/FALSE to 1/0

scott said:
both suggestions worked wonderfully and thanks for your help. can you explain
the "--" in functions? To me, it logically seems that is a double-negative,
but I am sure there is a different meaning.
Thanks!
Click to expand...
 
Back
Top