So to make it simple let’s say I have 2 columns, first is dates(A) and the second is numbers(B), the current date is 02/10 and it’s A1 I want a separate single cell to calculate B12, but I want that cell to calculate B22 when the date is 03/10(cell A2). Is that possible?
Please upload your ODF type sample file here.
Yes, it is possible. But I am not sure what do you want/need. Don’t do it so simple.
What when the date is 04/10? Or do you need it for only two dates?
The “separate single cell” is the same for both dates?
What do you means with “to calculate”? To show B12/B22 value?
Edit your question, and add there. Thanks.
basically i want avarage from filled cells, one new cell is filled everyday and so, everyday I need to edit the calculation to include one more cell, so I was thinking it’s their that, or make the calculation include 100 cells but ignore empty ones, but then one day I will go above 100 cells and will need to edit it again.
EDIT: calculation looks like this: =A9+(I9/AVERAGE(J3:J9)) I need the J9 to increase everyday, its J9 today, J10 tomorrow, J11 day after and so on.
Then take the formula =A9+(I9/AVERAGE($J$3:$J$1000000)) and you are on the safe side for the next 2700 years
but doesnt that include tiles that arent filled? And even if not some of those tiles are filled already but shoudnt be in the avarage yet.
Empty Cells doesnt count for AVERAGE.
If some of Cells in the Range are filled, but should not calculated →→ Problem caused by $User
problem is the cell has to be already filled, as it’s an sum of other cells with one of those cells being a fixed number plus its used for some conditional formatting, and even if i would make it empty and fill it everyday, that’s just shifting a problem from one thing to another as instead of editing the calculation, i would need to fill in a cell, a problem of having to edit the sheet still persists.
Following your first example (I can’t see your real spreadsheet):
In column C use the formula: =IF(A1<NOW();B1;"") and fill down as needed.
Next, add in another column the formula: =AVERAGE(C:C)
See LibreOffice Help on IF.
EDIT:
A better answer (no need of an auxiliary column):
=SUMIFS(B1:B15;A1:A15;"<="&TODAY())/COUNTIFS(A1:A15;"<="&TODAY())