|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
Thanks Purfleet, but the situation is not as you described.
20/06/2020 17/09/2020 15827 16478 9.2 |
#4
|
|||
|
|||
please adda work sheet with the data
|
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
Please post a sheet. It makes things MUCH easier for us.
Click " Go advanced" - Manage attachments and follow the wizard |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
Any interest in a VBA solution ?
|
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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. |
#13
|
|||
|
|||
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.
|
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 |
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 |
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 |