CODEWITHSUNDEEP
excelexcel-formulaspreadsheet
user24571186
user24571186

Reputation: 1

How to lock cells in a formula when a new row is inserted in a table

I am trying to find out the average amount of days it takes to respond within each month. I am using 2 worksheets, worksheet 1 is named 'Apps' and worksheet 2 is named 'Data'. Below is the table from worksheet 1 with the relevant information:

Column F (Date response submitted) Column G (Time taken (days)
02/05/2024 0
21/04/2024 -5
13/04/2024 2

Column F has the header 'Date response submitted' and Column G had the header 'Time taken (days). I have named the table 'AppsTable'.

Column F has single dates from January this year and Column G uses another formula to present the response time based on a due date, negative numbers indicate the response was sent before the due date and positive numbers indicate the response was sent after the due date. A zero means column F is empty and so has not been responded to yet.

This is a dynamic table and new rows will be added to the top row regularly with new dates inputted. I am trying to create an equally dynamic formula that updates as new rows are added but I cannot seem to figure out how to lock the cells so the formula incorporates the new 'F2' and 'G2' cells.

The table in worksheet 2 looks like:

Month Average
Jan 16.23
Feb 1.57

My original code looked like:

=AVERAGE(IF((MONTH(Apps!F2:F246)=1)*(YEAR(Apps!F2:F246)=2024),Apps!G2:G246, "<>0"))

I am aware that the $ locking function does not work for tables so I tried variations of:

=AVERAGE(IF((MONTH(AppTable[@[F2]:[F244]])=5)*(YEAR(AppTable[@[F2]:[F244]])=2024)*(AppTable[@[G2]:[G244]<>0), AppTable[@[G2]:[G244]))

but the 'there is a problem with this formula' pop up keeps appearing. All I am trying to do is lock the F2 and G2 cell so they remain as that, something I thought would be super simple!

There is probably such a simple solution but I do not know enough about excel functions to figure it out so TIA

Upvotes: 0

Views: 298

Answers (1)

Excellor
Excellor

Reputation: 860

So if I understand correctly, you're inserting rows on the top, thus moving your data down?

One way to make sure your formulas are referencing to the right cell, if that cell always stays on the same position, is using INDIRECT.

In your case, whenever you reference to G2, try using INDIRECT("G2") instead. Or, if you want it somewhat more dynamic/eassier to change, use a helper cell, say AA1 and use INDIRECT(AA1) and type G2 in cell AA1; however, you have to make sure that cell AA1 always remains on that position.

Upvotes: 0

Related Questions