Solved: SUM(IF (formula puzzle

Discussion in 'Business Applications' started by hnw, Feb 21, 2013.

Not open for further replies.

Joined:
Feb 21, 2013
Messages:
9
=SUM(IF((\$N\$16:\$N\$9500>0)*(\$J\$16:\$J\$9500=""),\$M\$16:\$M\$9500-\$G\$16:\$G\$9500)) produces a numeric result

=SUM(IF((\$N\$16:\$N\$9500>=0)*(\$J\$16:\$J\$9500=""),\$M\$16:\$M\$9500-\$G\$16:\$G\$9500)) produces a #VALUE error.

I am baffled. Using Excel X on a Mac.

2. etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
i get a #VALUE error for both

Joined:
Feb 21, 2013
Messages:
9
I thought you were supposed to help me, not the other way around
Only kidding. Still puzzled.

4. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
I got the same as etaf, for what it's worth. Wonder if that forced '=0' is kicking out the error.

=SUM(IF((\$N\$16:\$N\$9500>=0)

Joined:
Feb 21, 2013
Messages:
9
Thanks. I do know that if I try => instead of >= Excel objects and makes the change, with the resulting error mentioned. Apparently the "=" is a problem. A bug??

6. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
dunno if it's a bug or not; the pemdas stuff and excel has always bugged me.

You are correct, however, in that it is a rather odd one. No worries, however, we've got a bunch of good MS office folk around, someone will solve it.

Just out of curiousity, have you recreated it with Open Office or another spreadsheet app?

7. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
and it gets more interesting. Here's what Excel had to say about the top formula and the error:

File size:
27.5 KB
Views:
65
8. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
Oo didn't help, got a #NAME? error........weirder and weirder.

9. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
if you populate the formula fields with data, are you still getting the error?

Joined:
Feb 21, 2013
Messages:
9
Absolutely. I'll try it with Numbers and report back. Good suggestion.

11. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
thanks. I have meetings starting in about an hour, but I'll try to stick around.

Joined:
Feb 21, 2013
Messages:
9
Tried Numbers but formulas don't transfer and if I copy/paste them they show errors with no explanations. The syntax is very different apparently. Don't know what else to do. I'll just stay puzzled.

13. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
what version of office are you using?

14. valisModerator

Joined:
Sep 24, 2004
Messages:
76,952
never mind, just saw excel x on a mac......oooof. Not to strong on Mac stuff.......

Joined:
Feb 21, 2013
Messages:
9
That's ok. The Mac/Excel gurus don't have an answer either. Fortunately I have workarounds that give me the answers I need. I appreciate the effort.

As Seen On