Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 bugs in Excel
Summarize or Subtotal as it is called in US English requires the data to be
sorted. Regards, Tom Ogilvy "Paul" wrote in message ... Bug #1: SUMMARIZE I had a block of data 4,000 rows by 4 columns. Col A had 120 ID numbers, each repeated multiple times. I used the DATA SUMMARIZE feature to compile the data so that the 4000 rows should have boiled down to 120 rows, one for each ID. I kept getting wierd results. For example, ID 12345 was found in 212 rows. After summary, it should be only in one summary row, but it would be in 4 summary rows, each with wrong sums. Solution: I sorted the 4000 rows first by ID#. Then everything worked fine. Go figure. At least I had a workaround. BUG2: INDEX-MATCH Col X and Y consist of a title list and next to it a code: X....................Y Travel................11111 Food..................22222 Taxes.................33333 Floor Maintenance.....44444 General Maintenance...55555 Electrical Maintenance.66666 etc. CoL A cells have a drop-down list that is actually col X. Col B cells get their data from col Y via a FIND-MATCH formula. So if you select "Travel" in cell A1, B1 becomes "11111". It works 99.99% of the time. But if you select "General Maintenance" from the list, you get the wrong matching cell in B. If you change "General Maintenance" to "GM", it matches fine!!!!. It works fine with "GGGGGGG" and with "GGGGGG Maintenance". It fails with "Gen GGGG Maintenance". If you make it "xxx", it gives you <a different wrong match. So it is going through the list and finding a match, but the wrong match in a <few frustrating cases. This one has us stumped. Paul |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Bugs | Excel Worksheet Functions | |||
Hyperlink bugs | Excel Discussion (Misc queries) | |||
Two Bugs in Excel 2003 With Hyperlinks to Files | Excel Discussion (Misc queries) | |||
Excel 2007 Beta - 30 bugs | Excel Discussion (Misc queries) | |||
excel bugs | Excel Discussion (Misc queries) |