Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-17-2020, 10:25 AM
Tone Dialling Tone Dialling is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 97-2003
Novice
How can I accumulate a value in a given cell without creating a circular reference?
 
Join Date: Sep 2020
Posts: 6
Tone Dialling is on a distinguished road
Default How can I accumulate a value in a given cell without creating a circular reference?


I keep records of daily sunshine split into the four quarters of the year. For any given quarter, I have one Excel cell containing the accumulated hours of sunshine (to 1 d.p.) in that quarter. I update it daily. Whilst it is not tedious to do the addition in my head, what I would like to be able to do is to enter the day’s figure in one cell and have the accumulated hours updated automatically. Thus, suppose the accumulation always happens in cell Q3 and I place a particular day’s value, say 9.2, always in D3, I want Q3 to recalculate via the formula Q3 = Q3 + D3. But Excel won’t have that, as it contains a circular reference. What I suggest is a fairly simple task seems out of my grasp. How can I achieve the result I want?
Reply With Quote
  #2  
Old 09-17-2020, 11:07 AM
Purfleet Purfleet is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

you wont able able to sum the whole column if the total is in it, but you can have some kind of larger range you just put the total elsewhere in the workbook



Can you post an example so we can understand the layout a bit better?
Reply With Quote
  #3  
Old 09-17-2020, 11:24 AM
Tone Dialling Tone Dialling is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 97-2003
Novice
How can I accumulate a value in a given cell without creating a circular reference?
 
Join Date: Sep 2020
Posts: 6
Tone Dialling is on a distinguished road
Default

Thanks Purfleet, but the situation is not as you described.
20/06/2020 17/09/2020 15827 16478 9.2
Reply With Quote
  #4  
Old 09-17-2020, 11:35 AM
Purfleet Purfleet is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

please adda work sheet with the data
Reply With Quote
  #5  
Old 09-19-2020, 01:15 AM
Tone Dialling Tone Dialling is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 97-2003
Novice
How can I accumulate a value in a given cell without creating a circular reference?
 
Join Date: Sep 2020
Posts: 6
Tone Dialling is on a distinguished road
Default Sorry - trying again

I tried but accidentally terminated the post.
Here is part of my sheet:
20/06/2020 17/09/2020 15827 16478 9.2
I've pasted it in and things don't seem to behave when I try to improve the appearance.
The first entry is cell B33, containing the date of the start of the period, entered by hand
Second is C33, containing the formula =TODAY() and so showing the (then) current date
Third is D33, a value that is the reading on the first day of the period (i.e. the reading of the date in B33), entered by hand
Fourth is E33, showing the accumulated total value up to the day before the current date, entered by hand
Fifth is F33 showing the value of the current day, entered by hand. It is this value that I wish to add to E33 automatically so that I no longer have to do the arithmetic and enter the result by hand. Clearly = E33 + F33 will fail.
However, when I first tried this I got the error message about a circular reference but just now Excel inserted 0 into E33. Um
Reply With Quote
  #6  
Old 09-19-2020, 06:42 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 7 64bit How can I accumulate a value in a given cell without creating a circular reference? Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please post a sheet. It makes things MUCH easier for us.
Click " Go advanced" - Manage attachments and follow the wizard
Reply With Quote
  #7  
Old 09-19-2020, 09:37 AM
Tone Dialling Tone Dialling is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 97-2003
Novice
How can I accumulate a value in a given cell without creating a circular reference?
 
Join Date: Sep 2020
Posts: 6
Tone Dialling is on a distinguished road
Default Extract from my workbook

This data is to do with the amount of electricity I am getting from my solar panels. I didn't want to go into much detail when I started this as I thought I was just missing something obvious. Clearly if instead of an actual value entered by hand into E33, I enter the formula =E33+F33, I get a problem.
I think I have managed to attach the sheet although I can see no sign of it. It is only an extract but the rest is unconnected and irrelevant.
Attached Files
File Type: xlsx Circular ref problem.xlsx (9.6 KB, 6 views)
Reply With Quote
  #8  
Old 09-19-2020, 01:00 PM
Purfleet Purfleet is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Thanks, seeing the data is so much easier to know what is happening

No you cant add e33 to another number to get the result in e33 as excel doesnt know what e33 was prior to the latest 2 numbers

in my opinion you have 2 choices, have another cell to do the addtion and the type in back to E33 (which is rubbish) or create another sheet (or column on the same sheet) for the data entry then its a simple sum formula then copy paste speical on teh last day of the month - or even better a sumif

See the data on sheet2 and the 2 formulas in E & F
Attached Files
File Type: xlsx Circular ref problem_purfleet.xlsx (11.4 KB, 7 views)
Reply With Quote
  #9  
Old 09-19-2020, 03:37 PM
NoSparks NoSparks is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Any interest in a VBA solution ?
Reply With Quote
  #10  
Old 09-20-2020, 12:24 PM
Tone Dialling Tone Dialling is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 97-2003
Novice
How can I accumulate a value in a given cell without creating a circular reference?
 
Join Date: Sep 2020
Posts: 6
Tone Dialling is on a distinguished road
Default Tricky!

I want to update E33 every day. I've stuck an extra sheet into the workbook to do the daily update but it is hardly any improvement as I then have to start with that sheet, enter the value for the current day, the calculation of the new total then happens in the sheet but now I have to copy that value, return to the main sheet and paste special. This seems to work, but an elegant improvement it certainly is not. Also I am unfamiliar with SUMIF and from what I've discovered, I see no relevance.


It is starting to look as if there is no improvement available to the position I am currently in.
Thanks for your time,
Tony
Reply With Quote
  #11  
Old 09-20-2020, 12:36 PM
Purfleet Purfleet is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

i agree the first one is not great, the sum ifs is the way forward as that sums the column based on the end date.

Look at the formula in column F

I guess you type in the end date of the quarter in column C when the quarter ends (over writing the today() formula)? so E33 will sum everything in the column on sheet 2 upto the date in C33, you then drag it down to the next row at the start of the next quarter

This means you not have to add up the numbers but also you have a record of the daily amounts of sun if you needed to review or wanted to do further analysis
Reply With Quote
  #12  
Old 09-20-2020, 12:43 PM
NoSparks NoSparks is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Have a look at this VBA solution.
Just type number in E33 and it will add to D33 every time
until you enter a new date in B34 then it will be E34 that adds to D34.
It will always apply to the last used row according to column B.
Attached Files
File Type: xlsm Circular ref problem_vba.xlsm (22.0 KB, 6 views)
Reply With Quote
  #13  
Old 09-20-2020, 11:43 PM
Tone Dialling Tone Dialling is offline How can I accumulate a value in a given cell without creating a circular reference? Windows 10 How can I accumulate a value in a given cell without creating a circular reference? Office 97-2003
Novice
How can I accumulate a value in a given cell without creating a circular reference?
 
Join Date: Sep 2020
Posts: 6
Tone Dialling is on a distinguished road
Default

I've not done any programming for 40 years but if it can do better than where I'm at currently, then I'll give it a go.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Circular Reference - Getting Around It Pluviophile Excel 3 09-19-2020 01:19 PM
circular reference riothecat Excel 6 06-29-2017 10:56 AM
How can I accumulate a value in a given cell without creating a circular reference? Assign the value of a cell as a cell reference of another cell in Excel 2010 - How to? bharathkumarst Excel 7 10-13-2014 10:25 AM
How can I accumulate a value in a given cell without creating a circular reference? Circular Reference Warning in Excel Sheet. Rahmat_uk20 Excel 3 06-08-2012 02:31 AM
Circular Reference Errors BecciJane Project 2 06-01-2011 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:50 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