Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif Question | Excel Discussion (Misc queries) | |||
SUMIF Question | Excel Worksheet Functions | |||
SUMIF question (I think) | Excel Worksheet Functions | |||
SUMIF Question | Excel Discussion (Misc queries) | |||
sumif question 2 | Excel Programming |