formula result valid, but not appearing on spreadsheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MZ - 18 Apr 2007 18:13 GMT

Very strange problem...

I type SUMPRODUCT and then hit the "=" to bring up the wizard. I enter the
parameters and it gives me the correct answer at the bottom of the box.
Then when I hit ok, it gives me 0 in the actual spreadsheet. I click on the
0, check the formula, press "=" to bring up the wizard again and the formula
result is still correct.

So why isn't the result showing up on the spreadsheet??

Reply to this Message

Barb Reinhardt - 18 Apr 2007 18:32 GMT

What do you have for your formula?

> Very strange problem...
>
[quoted text clipped - 5 lines]
>
> So why isn't the result showing up on the spreadsheet??

Reply to this Message

MZ - 18 Apr 2007 18:33 GMT

> What do you have for your formula?

=SUMPRODUCT(IF(data!R2C6:R130C6=RC1,data!R2C5:R130C5,0),IF(data!R2C3:R130C3=R1C,1,0),IF(data!R2C4:R130C4=R1C[1],1,0))

"data" is another worksheet.

Reply to this Message

Niek Otten - 18 Apr 2007 21:06 GMT

I don't know about SUMPRODUCT specifically, but it does happen that the function wizard gives correct results and the cell
doesn't. In all cases I know of one or more of the arguments that should be numeric, were actually text.
Cells may look like numbers, but be text for Excel. Just formatting as number doesn't help; format AND re-enter the value (F2,
ENTER).
Of course it is annoying and misleading that the preview doesn't match the actual result.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Very strange problem...
|
[quoted text clipped - 5 lines]
|
| So why isn't the result showing up on the spreadsheet??

Reply to this Message

MZ - 18 Apr 2007 22:20 GMT

>I don't know about SUMPRODUCT specifically, but it does happen that the
>function wizard gives correct results and the cell
[quoted text clipped - 5 lines]
> Of course it is annoying and misleading that the preview doesn't match the
> actual result.

I've isolated it to just the first term in the SUMPRODUCT.

If I enter the following:

=SUM(IF(data!R2C6:R130C6=RC1,1,0))

I get 0 in the cell, but 4 in the preview.

The first term in the if statement is text, and so is the element in RC1.
They're supposed to be. But those aren't the values being returned. 1 and
0 are.

This doesn't make any sense!

Reply to this Message

MZ - 18 Apr 2007 22:35 GMT

>>I don't know about SUMPRODUCT specifically, but it does happen that the
>>function wizard gives correct results and the cell
[quoted text clipped - 19 lines]
>
> This doesn't make any sense!

Figured it out. For some reason, I needed to ctrl-shift-enter when I
entered it.

Reply to this Message