Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf not working over a long range
Hi,
I've used SumIf quite a lot to summaraise data in a spreadsheet, which has always worked fine, however I'm now trying to sum ranges over some 16,500 data points, and now I have this much larger range the calculations dont seem to work, whereas they always have over much shorter ranges. I'm just testing with a couple of numbers to see that it works and these tests are not working. Two examples: I expect this to return 10 =+SUMIF(GridCalcs!Z$3:Z$17001,"=0.1",GridCalcs!$D $3:$D$17001)-SUM(AG13:AG$15) because cell Z16544 = 0.1 and Cell D16544 = 10. I expect this to return 100 =+SUMIF(GridCalcs!BC$3:BC$17001,"<=-0.1",GridCalcs!$E$3:$E$17001)-SUM(O19:O$21) because cell BC16545 = -0.1 and Cell D16545 = 100 Both sums actually return zero. All the other values in column D are set to 0 for the purposes of the test. The problem only seems to occur with the 0.1 and -0.1 values, and of course if I set up a simple test in another workbook, that works fine. Oh, this is Excel 2003, SP2. The spreadsheet is trying to do quite a lot of results processing for me(!). The file size is a shade under 200MB. Any thoughts? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf not working over a long range
Thanks,
I know about the + and know its redundancy, its just an easy keyboard way of automatically getting into formula mode. I accept there's probably another easy way of doing this. The cells in the columns are pulled from another part of the spreadsheet which rounds to one decimal place, so I know they are all 0.1's and -0.1's, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf not working over a long range
I don't think the size of the range has anything to do with it. I could not
replicate your problem. Probably formatting. -- Don Guillett Microsoft MVP Excel SalesAid Software "Adam Lawrence Acoustics" <Adam Lawrence wrote in message ... Hi, I've used SumIf quite a lot to summaraise data in a spreadsheet, which has always worked fine, however I'm now trying to sum ranges over some 16,500 data points, and now I have this much larger range the calculations dont seem to work, whereas they always have over much shorter ranges. I'm just testing with a couple of numbers to see that it works and these tests are not working. Two examples: I expect this to return 10 =+SUMIF(GridCalcs!Z$3:Z$17001,"=0.1",GridCalcs!$D $3:$D$17001)-SUM(AG13:AG$15) because cell Z16544 = 0.1 and Cell D16544 = 10. I expect this to return 100 =+SUMIF(GridCalcs!BC$3:BC$17001,"<=-0.1",GridCalcs!$E$3:$E$17001)-SUM(O19:O$21) because cell BC16545 = -0.1 and Cell D16545 = 100 Both sums actually return zero. All the other values in column D are set to 0 for the purposes of the test. The problem only seems to occur with the 0.1 and -0.1 values, and of course if I set up a simple test in another workbook, that works fine. Oh, this is Excel 2003, SP2. The spreadsheet is trying to do quite a lot of results processing for me(!). The file size is a shade under 200MB. Any thoughts? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf not working over a long range
Thanks, I tried a Copy-PasteValues-RemoveFormatting to check, and indeed it
turns out that a simple subtraction 75.2(exactly) - 75.3(exactly) = -0.0999999999999943(!) So, that answers one question, but poses another one! Adam |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf not working over a long range
Answered my own other question by looking up answers to rounding problems.
For those that are interested, the topic "Doesn't add up correctly" gave good info. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working with a long list | Excel Discussion (Misc queries) | |||
too long sumif help | Excel Worksheet Functions | |||
SumIF is not working | Excel Worksheet Functions | |||
need help finding a Date range within long list | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |