We’d like to remind Forumites to please avoid political debate on the Forum.

This is to keep it a safe and useful space for MoneySaving discussions. Threads that are – or become – political in nature may be removed in line with the Forum’s rules. Thank you for your understanding.

📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
The Forum now has a brand new text editor, adding a bunch of handy features to use when creating posts. Read more in our how-to guide

Weird Excel calculation

Gers
Gers Posts: 13,574 Forumite
Part of the Furniture 10,000 Posts Photogenic Name Dropper
edited 3 February at 3:31PM in Techie Stuff

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ 93.59

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ 18.12

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ -

£ 169.90

When doing the Sum function (via the icon) I get a wrong answer All other cells are empty - I have checked and checked and checked again. Total should be £111.71 and I can't find this phantom £58.91. Even done a search.

Can anyone give an explanation and resolution please?

Comments

  • saajan_12
    saajan_12 Posts: 5,661 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker

    Could be hidden rows? I'd cut the sum range in half and check the sum matches what you'd expect manually. Then repeat to narrow down where the problem area is.

    Eg sum (top half of rows) = as expected? sum ( bottom half) = as expected.

    If one half doesn't, then repeat halfing that section and on and on.

  • Gers
    Gers Posts: 13,574 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper

    I deleted everything from the column and put it in again - seemed to have worked and the calculation worked correctly. Still can't work out why it was wonky in the first place.

  • saajan_12
    saajan_12 Posts: 5,661 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker

    My money is still on hidden rows. If you sum a range including a hidden row, it'll include that hidden value in the sum. If you highlight the lot including a hidden row and click delete, it'll also clear the hidden row, which could by why its now working.

  • Gers
    Gers Posts: 13,574 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    https://forums.moneysavingexpert.com/discussion/comment/81868548#Comment_81868548

    Thanks - but no, no hidden rows. Checked that. I've done the obvious thing and deleted the contents (only in those two rows) and put them back in. I hate it when a problem is 'solved' without a cause being found.

  • outtatune
    outtatune Posts: 865 Forumite
    Fourth Anniversary 500 Posts Name Dropper

    Custom cell formatting that's making the contents appear differently from their actual values?

  • sausage_time
    sausage_time Posts: 1,824 Ambassador
    Tenth Anniversary 1,000 Posts Name Dropper Photogenic

    Without access to the original sheet it is very hard to say what the problem was here.

    I’m a Forum Ambassador and I support the Forum Team on the Credit Cards, Savings & investments, and Budgeting & Bank Accounts boards. If you need any help on these boards, do let me know. Please note that Ambassadors are not moderators. Any posts you spot in breach of the Forum Rules should be reported via the report button, or by emailing forumteam@moneysavingexpert.com.
    All views are my own and not the official line of MoneySavingExpert.
★ ★ ★ Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 353.5K Banking & Borrowing
  • 254.1K Reduce Debt & Boost Income
  • 455K Spending & Discounts
  • 246.6K Work, Benefits & Business
  • 602.9K Mortgages, Homes & Bills
  • 178K Life & Family
  • 260.5K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16K Discuss & Feedback
  • 37.7K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.
Update Your Picture