Excel experts, need your help

[jangoux]

Guys,

I've been creating an Excel spreadsheet to sort out my incoming/outcoming money so I can understand better where my money is going. So, I created it, blah blah blah, but I want to add a function that skips or equals a cell to '0' if the formulae doesn't find an integer (or real) number. For example:

I have this row:
item a | 250
item b | 350
item c | 45
total : | =B1+B2+B3

Where B1 through B3 are the cells with the numbers above.

The thing is, if I put an symbol before the number to avoid a certain cell, the returned value is '#VALUE' instead of an actual numbers - because Excel can't make calculation with symbols (duh!). So is there an easy way to do this thing (if excel finds a symbol or something that is not an integer or a real number, suppose it is '0') without using VBA or Macros or something ? I thought of a loop, from my Pascal programming days, but I don't have a clue if Excel accepts it.

 

[Decimator]

try SUM (B1:B3)

 

[jangoux]

but SUM won't handle non-integer values.

I imagine it is something like:

c = integer
for B = 1 to 3
if B <> integer then B=0
C = C + B
else
C = C + B
return to loop

I just dont know how write this anymore or how to check if it is an integer or not.

 

[Decimator]

Hmm, ok I misunderstood the problem.

 

[jangoux]

I think this works:


Sub total1()
Dim total As Integer, valor As Integer
For b = 2 To 10
valor = ActiveCell.Value
If IsNumeric(valor) = False Then
total = total + 0
Else
total = total + valor
End If
Next b

End Sub

But how the fuck do I print a variable value into a Excel cell?

 

[MKS]

Perhaps I'm missing something obvious but why put a symbol before the number? What kind of symbols are we talking about?

Post a screenshot.

 

[jangoux]

For example, If I choose not to pay a certain bill, I'd put a ! or a * before the value of the bill. This way, I'll know the bill actually exists but won't be included in the monthly balance. Makes sense?

 

[jangoux]

OK, this works:

Sub total1()

Dim c As Range, d As Integer
For Each c In Range("B4:B9")
If IsNumeric(c) = True Then
d = d + c
Else
d = d + 0
End If
Next c
End Sub

Now I need to print the variable D into a cell. How do ?

 

[jangoux]

Dim c As Range, d As Integer
For Each c In Range("B4:B9")
If IsNumeric(c) = True Then
d = d + c
Else
d = d + 0
End If
Next c
MsgBox d
c = d
ActiveSheet.Cells(26, 2).Value = d
End Sub

 

[MKS]

If that works for you. I would have went a different route. Put the symbols in another column and use an "if" statement to determine to add (on not).

 

[MKS]

Status Amount To Pay
Pay $15.00 $15.00
Not $25.00 $-
Pay $30.00 $30.00
$70.00 $45.00
Actual Pay

Using this formula in column C =IF(A2="pay",B2,1)

 

[MKS]