Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-08-2021, 08:09 AM
silverman166 silverman166 is offline Sum function not changing its value when items being summed are changed Windows 7 64bit Sum function not changing its value when items being summed are changed Office 2007
Advanced Beginner
Sum function not changing its value when items being summed are changed
 
Join Date: Nov 2019
Posts: 31
silverman166 is on a distinguished road
Default Sum function not changing its value when items being summed are changed

In my spreadsheet which has been produced over many years now, I have two adjacent columns of previous and current energy meter readings, input manually each month, and in the third column I simply subtract the two to obtain a third figure to obtain the usage over that month.




At the end of the year I then sum the third column to give total usage over the year.


Simple really, except that a new problem has just arisen. The Total figure at the bottom of the third column produces a result, but I find that if I change a reading in the second column, whilst the subtraction result changes as expected, the Total figure bizarrely remains unchanged.


I have no idea what has seemingly suddenly caused this, but whatever it is the same is found elsewhere in the spreadsheet where identical calculations are performed.


If I produce a simple table doing the same functions from scratch in an empty part of the spreadsheet, it performs as it should, so I deduce from this that there has been some change to affect the rest of the spreadsheet.


The question is exactly what, and can it be recovered!


Any suggestions gratefully received.


(I tried to copy a part of the spreadsheet to post here, but was unsuccessful. Hopefully the above will suffice.)
Reply With Quote
  #2  
Old 03-08-2021, 09:33 AM
Logit Logit is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

You really need to post your workbook (no confidential data please) for review.
Reply With Quote
  #3  
Old 03-08-2021, 12:34 PM
silverman166 silverman166 is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Advanced Beginner
Sum function not changing its value when items being summed are changed
 
Join Date: Nov 2019
Posts: 31
silverman166 is on a distinguished road
Default

OK, the attached is what I get when I copy part of the problem spreadsheet and paste it into a new workbook, but it does not show the problem as I had it, in that changing the entry in D9 does not change E9 as it should, whereas in the original that did change to the correct figure, but the Total shown in E16 did not change.


This is why I didn't post it in the first place, but if by any chance it helps, it's now here. If there's a better way to copy please let me know.


The formulae will not show here, but they look perfectly OK in the malfunctioning original.
Attached Files
File Type: xlsx Test.xlsx (9.1 KB, 7 views)
Reply With Quote
  #4  
Old 03-08-2021, 12:39 PM
Logit Logit is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

What formulas are you using ... and where are these formulas pasted ?

It would be best to post your original workbook that is having the issues. That way we can work
with the offending sheet/s & formula/s.

Oftentimes, rebuilding the workbook from scratch eliminates all of the errors ... which is an option you also have - rebuild the workbook from scratch.
Reply With Quote
  #5  
Old 03-09-2021, 02:13 AM
silverman166 silverman166 is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Advanced Beginner
Sum function not changing its value when items being summed are changed
 
Join Date: Nov 2019
Posts: 31
silverman166 is on a distinguished road
Default

Thanks, Logit.


The formulae used were the basic subtract and sum functions, which as I said work fine as a new table within an empty space on the spreadsheet concerned.



I'm unwilling to attach the whole spreadsheet, as there is private content within it, and as previously indicated copying the section concerned and pasting to a new workbook produces erroneous results not representing the problem.


I fully appreciate the need to have a workable sheet for you to investigate with, but I see no way to do that.

Now I'm aware of the problem I can live with it, in that if I ever change a value, I will need to manually re-enter the sum function, as I've now found that does work. It would have been useful to know the cause, but it seems I can't.


So thanks so much for your interest, but I can't see how to take this further.
Reply With Quote
  #6  
Old 03-09-2021, 09:07 AM
Logit Logit is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Do not include confidential data. Utilize substitute data that still produces the error.
Reply With Quote
  #7  
Old 03-10-2021, 04:23 AM
silverman166 silverman166 is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Advanced Beginner
Sum function not changing its value when items being summed are changed
 
Join Date: Nov 2019
Posts: 31
silverman166 is on a distinguished road
Default

Thanks, Logit


Instead of copy and paste from one sheet to another, I've taken a copy of the faulty spreadsheet and effectively cropped it to show an area which displays the problem, then saved it as Test 1 and attached it to this post.


It does demonstrate the problem and will display the simple formulae used too.


Hope you can make something of it!
Attached Files
File Type: xlsx Test 1.xlsx (31.1 KB, 8 views)
Reply With Quote
  #8  
Old 03-10-2021, 04:56 AM
ArviLaanemets ArviLaanemets is offline Sum function not changing its value when items being summed are changed Windows 8 Sum function not changing its value when items being summed are changed Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

And what is the problem? You corrected the reading for one month (e.g. for a value Correction). As result returned values in column 'kWh used' are changed for this month (the change value equals Correction), and for next month (the change value equals -1*Correction).

The total of 'kWh used' remains same (unless you correct entry in 1st or last row of your table), as total change will be Correction - Correction = 0

Another way to look at this: The total is 'Current rdg' from 1.03.2021 minus 'Last rdg' from 29.02.2020, i.e 15175 - 11298 =3877. Whatever happens with values between, doesn't affect this result at all!
Reply With Quote
  #9  
Old 03-10-2021, 06:25 AM
silverman166 silverman166 is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Advanced Beginner
Sum function not changing its value when items being summed are changed
 
Join Date: Nov 2019
Posts: 31
silverman166 is on a distinguished road
Default

I'm afraid the problem is in my head!!


My only defence is that advancing years seem to have dulled what powers of intellect I ever had! I confess it took me some time to follow your solution, but eventually the penny dropped!



So, very many thanks for following this, and pointing the solution to the 'non-problem' out, and apologies for wasting your and particularly Logit's time.


I really resent the loss of the time that I have spent on this, but at least my faith in Excel has now been restored, and I've learned a bit of new stuff on the way!


To Logit - thanks for your interest and persistence - you may have gathered that had you not replied to my last post, I would have given up!


So once again, very many thanks!!
Reply With Quote
  #10  
Old 03-10-2021, 09:00 AM
Logit Logit is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Glad you found an answer.

"advancing years" ??? When does that begin ? I'm 70 and still stumble around quite effectively, while embarrassing myself to others. What were we talking about ?

"To Logit - thanks for your interest and persistence". That is the key (at least for me) 'persistence' .... never ever give up !

Cheers.
Reply With Quote
  #11  
Old 03-10-2021, 04:32 PM
silverman166 silverman166 is offline Sum function not changing its value when items being summed are changed Windows 10 Sum function not changing its value when items being summed are changed Office 2007
Advanced Beginner
Sum function not changing its value when items being summed are changed
 
Join Date: Nov 2019
Posts: 31
silverman166 is on a distinguished road
Default

Thanks for that!



Cheers!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Repeat Function By Changing One Variable abbani Excel 3 10-04-2017 12:41 AM
2016 Excel Subtotal Function Changed CindyPauls Excel 0 08-31-2017 11:44 AM
Sum function not changing its value when items being summed are changed List of 30 items with Logos, transition the 30 items blexann PowerPoint 1 11-03-2016 08:55 AM
Deleting items in folders doesnt send them to deleted items! marshy365 Outlook 0 07-29-2016 03:19 AM
Outlook 2007 Saved sent items list only holds the last ten items david.peake Outlook 0 06-01-2010 07:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft