Formula anomaly

normpam

Active Member
Joined
Oct 30, 2002
Messages
266
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.

=IF(AND(SUM(C3:G3)=0,SUM(ABS(C3:G3)<>0)),"GOOD","BAD")

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.

Help please!
 

Some videos you may like

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
Joined
Oct 10, 2011
Messages
4,685
Office Version
  1. 365
Platform
  1. Windows
You have a parenthesis out of place.
Try:
IF(AND(SUM(C3:G3)=0,SUM(ABS(C3:G3))<>0),"GOOD","BAD")
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,908
Office Version
  1. 2010
Platform
  1. Windows
And if you use SUMPRODUCT, you can normally-enter the formula (just press Enter as usual) instead of array-entering it.

=IF(AND(SUM(C3:G3)=0, SUMPRODUCT(ABS(C3:G3))<>0), "good", "bad")
 

normpam

Active Member
Joined
Oct 30, 2002
Messages
266
You have a parenthesis out of place.
Try:
IF(AND(SUM(C3:G3)=0,SUM(ABS(C3:G3))<>0),"GOOD","BAD")
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
Joined
Jun 12, 2014
Messages
50,807
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It needs to be
VBA Code:
Range("B8").Formula2 = "=SUM(abs(c8:bz8))"
Also please update your account details to show which version of Excel you are using, as this helps us to help you.
 

normpam

Active Member
Joined
Oct 30, 2002
Messages
266
It needs to be
VBA Code:
Range("B8").Formula2 = "=SUM(abs(c8:bz8))"
Also please update your account details to show which version of Excel you are using, as this helps us to help you.
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
Joined
Jun 12, 2014
Messages
50,807
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,659
Messages
5,573,453
Members
412,529
Latest member
cTatch
Top