Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Winston-Salem, North Carolina, USA
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Spreadsheet created in Excel 2007, saved as .xls file.

    I have a report for a fundraising campaign, which I have updated every week for months in exactly the same format. The database I use to track the data exports into Excel a list of the pledge totals per person with every payment per person. I then consolidate the data into one line per donor, adding up all the payments. To make sure I haven't made an error in the consolidation, I have used conditional formatting on each total at the top of the spreadsheet so that if the total varies from what it should be, the cell will be filled with red. Each week, I change the formula for the conditional formatting to reflect changes in total pledges (if any - mostly there aren't changes at this stage in the fundraising campaign), total payments and total balance due. I have a total for pledges, which totals all cells below, a total for payments, which totals all cells below, and a total for balance, which subtracts payment total from pledge total. I have attached a copy of the spreadsheet so you can see what I've done. This is not the entire spreadsheet - I've removed the top 13 lines, which contained various bits of information about the data, and removed any identifying information. However, even after removing all of that, the conditional formatting still misbehaves as it does in the full spreadsheet.

    Starting with last week's report, the conditional formatting on the Balance (total) cell began to fail. The cell is filled with red regardless of the value in the cell. I have tested a number of things. First, I removed all the lines that make up the heading to see if there was any data which could cause a conflict. Second, I changed the formula in the Balance total cell so that it would add all the lines below rather than subtracting one total from another. Third, I tried applying the conditional formatting to other cells, both cells that contained the same total (which shows up a couple of times in the header) and in blank cells, which referred to the total cell for the conditional formatting. Fourth, I rebuilt the entire spreadsheet, copying only the data, rebuilding all formulas manually. The conditional formatting on the Balance cell only still fails.

    I'm just about to tear my hair out over this. Does anybody have any suggestions about what might be causing this problem? Thanks so much.

    Lee Morgan
    Attached Files Attached Files

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Comments...
    1. Your attachment has conditional formatting applied to only 3 cells: A3:C3
    2. Column C is number formatted to show negative numbers in Red.
    3. Excel versions prior to XL2007 are limited to 3 conditional formats per cell.
    Things happen to CF when converting from xl2007 to an earlier version.
    4. My free Excel add-in "Formats & Styles"... Excel add-ins
    lists or removes: Styles, number formats, conditional formatting.
    Example
    '--
    Jim Cone
    Portland, Oregon USA

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Lee,

    another example of Excel not doing what you expect. I have not checked all your formulas, but I did change the formatting on cell C3 to Number, squintillion decimal places. This showed that Excel was calculating a value of n.050000001. So, this did not match your target of n.0500000000.

    Ok?

    Jules

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Winston-Salem, North Carolina, USA
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Jules. That would explain it, although how that final digit got there when the numbers produced by the database are all entered manually in dollars and cents with only two digits after the decimal is a mystery to me. I'll try making the balance due calculation Round() rather than Sum() and see if that fixed it. Thanks so much for the reply and for your idea.

    Lee

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Winston-Salem, North Carolina, USA
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Changing the formula in the total balance cell to round() did fix the problem. Thanks!

    Lee

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Lee,

    here's a link to an article that highlights how Excel occasionally cannot add 2 plus 2 to make 4.

    Excel additions errors

    It's all to do with Excel not using real numbers to add up with (or something like that!). As you say, rounding should fix the problem.

    Jules

  7. #7
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Hi Lee,

    You may also want to consider putting the figure you want to reconcile to in a separate cell and using that cell reference in the conditional formatting. It saves having to go into Conditional Formatting each time you enter new data.

    Nigel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •