# Formula anomaly

#### normpam

##### Active Member
I think I'm going to lose my mind.... still working on what seems to be a simple issue.

C3 = 10
D3 = 15
E3 = -30
F3 = 15
G3 = -10

The formula =Sum(C3:G3) results in zero
The formula =SUM(ABS(C3:G3)) results in 80

I would expect the result of the following formula to be "GOOD" - but it results in "BAD" and I cannot figure out why.

Both conditions are true - the sum of C3:G3 is zero, and the sum of the absolute values of those cells does NOT equal zero.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### AhoyNC

##### Well-known Member
You have a parenthesis out of place.
Try:

#### joeu2004

##### Well-known Member
And if you use SUMPRODUCT, you can normally-enter the formula (just press Enter as usual) instead of array-entering it.

#### normpam

##### Active Member
You have a parenthesis out of place.
Try:
OMG... I feel like the scarecrow from Wizard of Oz... 'if I only had a brain'!
Thanks! While trying to get a solution I came up with another anomaly. When I put the following formula in a cell using VBA it keeps putting in an @ sign all by itself before the C8 in the formula. Very weird. What is more weird is that it is a phantom character. When I do a 'Find' in the Excel sheet it says there is no such character.

Range("B8").Value = "=SUM(abs(c8:bz8))"

#### Fluff

##### MrExcel MVP, Moderator

It needs to be
VBA Code:
``Range("B8").Formula2 = "=SUM(abs(c8:bz8))"``

#### normpam

##### Active Member
It needs to be
VBA Code:
``Range("B8").Formula2 = "=SUM(abs(c8:bz8))"``
Not sure if 'WOW' fully expresses it..... OK.... I just read a bit on Google about 'Formula2', but not clear to me what it does. Is there a simple explanation you could share?
Thanks!!

#### Fluff

##### MrExcel MVP, Moderator
It was added to VBA to allow for Dynmaic aware excel, so that you don't get the @ sign appearing in formulae.

Please don't forget to update your account details to show your version of Excel & then scroll down & click save.

Replies
6
Views
47
Replies
28
Views
251
Replies
0
Views
108
Replies
9
Views
195
Replies
3
Views
133