SUMPRODUCT calcs correctly on one PC, but incorrectly on another
 

The following formula calculates correctly on my work PC, but not on
my home PC. Do you know the reason behind this? What can I do to fix
it? Any help will be much appreciated.

=IF(COUNTIF('Cash Flows'!$C$2:$C$50000,$A1)=0,0,
(SUMPRODUCT((MAX(('Cash Flows'!$A$2:$A$50000)*('Cash Flows'!$C$2:$C
$50000=$A1))))-SUMPRODUCT(SMALL(('Cash Flows'!$A$2:$A$50000)*('Cash
Flows'!$C$2:$C$50000=$A1),(COUNTIF('Cash Flows'!$C$2:$C$50000,"<"&
$A1)+1)-COUNTIF('Cash Flows'!$C$2:$C$50000,$A1))))+1)

Kevin

SUMPRODUCT calcs correctly on one PC, but incorrectly on another
 

On Wed, 13 Feb 2008 06:14:46 -0800 (PST), DoooWhat
wrote:

The following formula calculates correctly on my work PC, but not on
my home PC. Do you know the reason behind this? What can I do to fix
it? Any help will be much appreciated.

=IF(COUNTIF('Cash Flows'!$C$2:$C$50000,$A1)=0,0,
(SUMPRODUCT((MAX(('Cash Flows'!$A$2:$A$50000)*('Cash Flows'!$C$2:$C
$50000=$A1))))-SUMPRODUCT(SMALL(('Cash Flows'!$A$2:$A$50000)*('Cash
Flows'!$C$2:$C$50000=$A1),(COUNTIF('Cash Flows'!$C$2:$C$50000,"<"&
$A1)+1)-COUNTIF('Cash Flows'!$C$2:$C$50000,$A1))))+1)

Kevin

Assuming the formulas are the same, but the results are different, then there
is a difference either in your data or in the manner in which Excel is
interpreting your data.

But I'm not very good at mind-reading. You described neither what you mean by
functioning incorrectly, nor the type of data you are inputting to the formula.

Perhaps you have a different date system on the two PC's; perhaps one is set to
use Lotus transition?

Perhaps you could rephrase your question and supply relevant information?
--ron

SUMPRODUCT calcs correctly on one PC, but incorrectly on another
 

On Feb 13, 6:14 am, DoooWhat wrote:
The following formula calculates correctly on my work PC,
but not on my home PC. Do you know the reason behind
this? What can I do to fix it? Any help will be much appreciated.

It would be helpful to know what you mean by "correctly" and "not".
That is, what result do you get on one PC, and what different result
do you get on the other PC?

It might also be helpful to know what is different about the two PCs.
In particular, is one Intel based and the other AMD based? What
revisions of Excel is each PC running, including patches? What
revision of Windows is each PC running? Etc.

Finally, if you have Excel 2003, try using ToolsFormula
AuditingEvaluate Formula to break down the evaluation of the parts of
the formula. At what point exactly are the two PCs different, and
how?

Wild speculation: Of course, the simplest explanation is that one or
more the ranges used in the formula do not contain the same values on
the two PCs. What makes you think they do or should?

A common source of such an error is that you opened the wrong workbook
on one PC, for example a workbook with the same file name, but in the
wrong path.

HTH.


----- original posting -----

On Feb 13, 6:14*am, DoooWhat wrote:
The following formula calculates correctly on my work PC, but not on
my home PC. *Do you know the reason behind this? *What can I do to fix
it? *Any help will be much appreciated.

=IF(COUNTIF('Cash Flows'!$C$2:$C$50000,$A1)=0,0,
(SUMPRODUCT((MAX(('Cash Flows'!$A$2:$A$50000)*('Cash Flows'!$C$2:$C
$50000=$A1))))-SUMPRODUCT(SMALL(('Cash Flows'!$A$2:$A$50000)*('Cash
Flows'!$C$2:$C$50000=$A1),(COUNTIF('Cash Flows'!$C$2:$C$50000,"<"&
$A1)+1)-COUNTIF('Cash Flows'!$C$2:$C$50000,$A1))))+1)

Kevin