Scary and Confusing Formula Problem :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Scary and Confusing Formula Problem

Yesuslave
Board Regular

Joined: 19 Sep 2003
Posts: 97
Location: New Jersey
Flag:

Status: Offline

Scary and Confusing Formula Problem

Hi all,

OK, this is a crazy thing in excel that neither my partner nor I can figure out.

I have a formula that I have inserted into a cell using a macro that basically simply adds up cells and returns the sum. It will not return anything other than 0 if one of the cells it adds up has a sum of 0. Why is this?

Has anyone ever heard of somthing like this? i mean, it makes sense if it is multiplication, but this is addition. All of the cells are formatted exactly the same. Also, if I copy the formula in the cell that works, and then paste it on top of itself (effectively simply replacing it with itself) it returns 0, when before it had the accurate sum.

HEEEEELLLLLLLPPPPPPP!!!!!

Joshua Wise

Fri Sep 19, 2003 7:48 pm

just_jon
MrExcel MVP

Joined: 04 Sep 2002
Posts: 4007
Location: Huntsville AL
Flag:

Status: Offline

Re: Scary and Confusing Formula Problem

Can you post the macro?

EDIT: And the formula which yields 0 when copied over itself.
_________________
just_jon
Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Fri Sep 19, 2003 8:01 pm

Yesuslave
Board Regular

Joined: 19 Sep 2003
Posts: 97
Location: New Jersey
Flag:

Status: Offline

Re: Scary and Confusing Formula Problem

This code isn't annotated yet, so it might be somewhat confusing. But the point is that the code looks for cells that contain things like "=SUM(F5:F10)" which may or may not contain a total of 0.00. If it does contain a sum of 0.00, the whole total in the end ends up as zero, even if other cells in the Sum have non zero totals.

Function FinalTotals(strMarker As String)

Dim strMarkerCell As String
Dim i As Integer
Dim x1, x2, x3, x4, x5, x6 As Integer
Dim y1, y2, y3, y4, y5 As Integer
Dim strFormula As String

Range("C2").Select
i = 0
x1 = 0
x2 = 0
x3 = 0
x4 = 0
x5 = 0
x6 = 0

Do Until ActiveCell.Text = strMarker
ActiveCell.Offset(1, 0).Select
Loop

Do Until i = 6
Do Until ActiveCell.Text = strMarker
ActiveCell.Offset(1, 0).Select
Loop
If x6 = 0 Then
x6 = ActiveCell.Row
ElseIf x6 <> 0 And x5 = 0 Then
x5 = ActiveCell.Row
ElseIf x5 <> 0 And x4 = 0 Then
x4 = ActiveCell.Row
ElseIf x4 <> 0 And x3 = 0 Then
x3 = ActiveCell.Row
ElseIf x3 <> 0 And x2 = 0 Then
x2 = ActiveCell.Row
ElseIf x2 <> 0 And x1 = 0 Then
x1 = ActiveCell.Row
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-1, 0).Select

y1 = x1 - x2
y2 = x1 - x3
y3 = x1 - x4
y4 = x1 - x5
y5 = x1 - x6

strFormula = "=R[-" & y1 & "]C+R[-" & y2 & "]C+R[-" & y3 & "]C+R[-" & y4 & "]C+R[-" & y5 & "]C"
varVar = InsertToCells(strFormula)

End Function

Function InsertToCells(strFormula As String)

varVar = CellPrep(3)
ActiveCell.Formula = strFormula

varVar = CellPrep(2)
ActiveCell.Formula = strFormula

varVar = CellPrep(8)
ActiveCell.Formula = strFormula

i = 0
Do Until i = 5
varVar = CellPrep(2)
ActiveCell.Formula = strFormula
i = i + 1
Loop

End Function
Function CellPrep(intOffset As Integer)
ActiveCell.Offset(0, intOffset).Select
With Selection.Font
.Underline = xlUnderlineStyleDoubleAccounting
End With
Selection.NumberFormat = "0.00"
End Function
_________________
I can't decide whether I want to be a Ludite or a technogeek.

Fri Sep 19, 2003 8:10 pm

Richie(UK)
MrExcel MVP

Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag:

Status: Offline

Re: Scary and Confusing Formula Problem

For reference this query has also been posted here:

_________________
««« ¤ Richie ¤ »»»

Tue Sep 23, 2003 7:50 pm

Yesuslave
Board Regular

Joined: 19 Sep 2003
Posts: 97
Location: New Jersey
Flag:

Status: Offline

Re: Scary and Confusing Formula Problem

Though i did not find out the answer to this question, I did create a loop that checks to see if a cell's sum equals 0. If it does, I did not include it in the formula.

Thanks anyway...looking forward to learning a lot here.

Joshua Wise
_________________
I can't decide whether I want to be a Ludite or a technogeek.

Wed Sep 24, 2003 8:32 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum