SUMIF Question
 

Greetings,

I have a Conditional Format with a couple of SUMIF's in it, The SUMIF
does not seem to notice the polarity of the numbers it is summing and
is giving erroneous results. Example: if the data in the selected
rows and 1st referenced column are 10, 120, 20, -180 and 40. The
result should be 10. It should match up with the 2nd summed column
which has only a 10. They do not! The formula works if all numbers
are positive. Here are the formulas in the Conditional Format:

2nd conditional formula:

=(SUMIF($X$2:$X$250,$X2,$E$2:$E$250)-SUMIF($X$2:$X$250,$X2,$F$2:$F$250))0
Turns cell pink

3rd conditional formula:

=(SUMIF($X$2:$X$250,$X2,$F$2:$F$250)-SUMIF($X$2:$X$250,$X2,$E$2:$E$250))0
Turns cell light blue

Column X is the reference column. Column E is first column summed and
Column F is last column summed. The totals of summed column E are
supposed to equal the summed totals of column F for any given value in
column X.

That is what it is doing except for any negative numbers.

Is there any way to fix this?

Any help would be most appreciated.

-TIA

-Minitman

SUMIF Question
 

Could it be a rounding issue - are the values the results of formulas - What
happens if you try

=ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250)-SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2)0

or

=(ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250),2)-ROUND(SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2))0

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Minitman" wrote in message
...
Greetings,

I have a Conditional Format with a couple of SUMIF's in it, The SUMIF
does not seem to notice the polarity of the numbers it is summing and
is giving erroneous results. Example: if the data in the selected
rows and 1st referenced column are 10, 120, 20, -180 and 40. The
result should be 10. It should match up with the 2nd summed column
which has only a 10. They do not! The formula works if all numbers
are positive. Here are the formulas in the Conditional Format:

2nd conditional formula:

=(SUMIF($X$2:$X$250,$X2,$E$2:$E$250)-SUMIF($X$2:$X$250,$X2,$F$2:$F$250))0
Turns cell pink

3rd conditional formula:

=(SUMIF($X$2:$X$250,$X2,$F$2:$F$250)-SUMIF($X$2:$X$250,$X2,$E$2:$E$250))0
Turns cell light blue

Column X is the reference column. Column E is first column summed and
Column F is last column summed. The totals of summed column E are
supposed to equal the summed totals of column F for any given value in
column X.

That is what it is doing except for any negative numbers.

Is there any way to fix this?

Any help would be most appreciated.

-TIA

-Minitman


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004

SUMIF Question
 

Hey Ken,

There should not have been but II guess there was.

Your modification works like a charm

Thank you.

-Minitman

On Sat, 6 Nov 2004 20:13:01 -0000, "Ken Wright"
wrote:

Could it be a rounding issue - are the values the results of formulas - What
happens if you try

=ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250)-SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2)0

or

=(ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250),2)-ROUND(SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2))0

SUMIF Question
 

Anytime you do calculations in Excel that involve decimals you start to make it
hard if you need things to be exactly equal. Comes down to the problem of
expressing fractional numbers in binary format, and so it's usually easiest to
just round the data off using ROUND. Your data may well have been only
0.0000000000001 off due to the problem, but that's enough under certain
conditions to get a FALSE return with an x = x type scenario.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Minitman" wrote in message
...
Hey Ken,

There should not have been but II guess there was.

Your modification works like a charm

Thank you.

-Minitman

On Sat, 6 Nov 2004 20:13:01 -0000, "Ken Wright"
wrote:

Could it be a rounding issue - are the values the results of formulas - What
happens if you try

=ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250)-SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2)0

or

=(ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250),2)-ROUND(SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2))0



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004

SUMIF Question
 

Thanks Ken,

I'll remember that.

-Minitman

On Sat, 6 Nov 2004 22:17:05 -0000, "Ken Wright"
wrote:

Anytime you do calculations in Excel that involve decimals you start to make it
hard if you need things to be exactly equal. Comes down to the problem of
expressing fractional numbers in binary format, and so it's usually easiest to
just round the data off using ROUND. Your data may well have been only
0.0000000000001 off due to the problem, but that's enough under certain
conditions to get a FALSE return with an x = x type scenario.