Formula anomaly

[normpam]

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!

 

[AhoyNC]

You have a parenthesis out of place.
Try:
IF(AND(SUM(C3:G3)=0,SUM(ABS(C3:G3))<>0),"GOOD","BAD")

 

[joeu2004]

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]

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]

It needs to be

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]

It needs to be

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]

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.