• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

End of col

J

jackmanjls

Member
I have the following code: =K36/COUNT(N20:N26)

This is used in a calc. As I add more rows I want the N26 to increment accordingly.
Assume that I add 2 rows the N26 will go to N28. Currently I go in the ss and update to N28.
How can I get this to auto update as I add new rows?
 
Hi Jack,

Try the below formula, i think it will resolve your query.

=k36/COUNT(N20:INDIRECT(ADDRESS(COUNTA(N20:N500),1)))


Regards
Abdul Matheen
 
That does work but it creates another potential issue.
Here's the problem. Let's assume that my current ss only uses N20-N50 rows for my calc, as I continue in this year I could add anywhere from 0 to 3 rows/day. So, what i want to do is limit the calc to only the current N20-N50 rows. I want to make this flexible enough so that down the road if I want to use those col N's that are greater than my current N range that it won't generate an error.

So, I want to replace the N500 with the N for the last row in my calc (in this case N50).
 
SirJB7 said:
Hi, jackmanjls!
Are there any empty cells in the range N20:Nxxx?
Regards!
Click to expand...
This calc occurs in the middle of the ss. Assume there's data in N20:N22 and the sum results is in N26. Assume that I add 1 col N23 the results will now be in N27.
 
Hi, jackmanjls!
I could assume that but you didn't answered my question, and without that answer I don't know if the solution I'm thinking about will work or not. Just in case it's required, when I wrote N20:Nxxx, xxx wasn't necessarily intended to mean the last used cell of column N but the last used in the range that you want to expand: N26 in the 1st post.
Regards!
 
SirJB7 said:
Hi, jackmanjls!
I could assume that but you didn't answered my question, and without that answer I don't know if the solution I'm thinking about will work or not. Just in case it's required, when I wrote N20:Nxxx, xxx wasn't necessarily intended to mean the last used cell of column N but the last used in the range that you want to expand: N26 in the 1st post.
Regards!
Click to expand...
Thanks for your response. I think I can make this clear and answer your q.

Initially I have data in N20:N22 and in N26 (last used) I have sum(N20:N22). So the entire range of this portion of the ss is N20 to N26. It's important to note that there's data, that I don't want to include, before and after this range.

Now I add 1 row with data. The new data range is N20:N23 and in N27 (new last used) I have sum(N20:N23). As I add rows the entire range increases by each row that's added. In the end I will add up to 100 rows and each row addition will increase the overall range of the calc.

What I want to accomplish is that as I add rows the sum will auto update with the new added data.
Hope this is clear.
 
Hi ,

You mentioned that your data range used in the SUM function is N20:N22 , while the SUM formula itself is in N26.

Is this correct ?

If yes , what is there in cells N23 through N25 ?

If there is nothing , and by adding data , you mean that you are inserting rows and putting data in the inserted cells , then nothing should need to be done , if you change your formula to include the empty cells , and make it =SUM(N20:N25) ; now if you insert a row , the formula will change to =SUM(N20:N26).

Narayan
 
NARAYANK991 said:
Hi ,

You mentioned that your data range used in the SUM function is N20:N22 , while the SUM formula itself is in N26.

Is this correct ?

If yes , what is there in cells N23 through N25 ?

If there is nothing , and by adding data , you mean that you are inserting rows and putting data in the inserted cells , then nothing should need to be done , if you change your formula to include the empty cells , and make it =SUM(N20:N25) ; now if you insert a row , the formula will change to =SUM(N20:N26).

Narayan
Click to expand...
At first I thought it would be easier on this forum to just take a simple SUM formula and then apply to my situation but it might be more informative to look at the real layout.

So, here's what I actually have.
N20:N22 has the data that I want to COUNT.
N23 is blank.
N24 to N25 have other formulas.
My formula in N25 is a SUM of other cells, not N20:N22.
In N26 I have N25/COUNT(N20:N22).
To add a row I put the cursor on N23 and just add the row.
I check to see if the COUNT formula in N26 has updated and it hasn't.

Therefore, I think I need some dynamic method to update N25's COUNT as I add rows.
 
Hi ,

This is OK with what I had posted earlier ; if you change your COUNT formula to :

=N25/COUNT(N20:22)

then each time you place the cursor in the blank cell ( to start with it would be N23 ) , and insert a row , the formula will change automatically.

Narayan
 
Narayan,

I ran a little test ss to verify what you said.

This is what I did.
In E1:E3 I put data.
E4 is empty.
E5 has the number 6
E6 has E5/COUNT(E1:E3)
Placing the cursor at E4 and adding a row did not increment the COUNT. The formula did not change automatically.

If it's suppose to then I'm doing something wrong and don't know what it is.
 
Hi ,

Sorry , my mistake. I copied your formula but forgot to change it.

If the cell E4 is empty , the formula should include that cell ; put in your formula as :

=E5/COUNT(E1:E4)

Now , if you place the cursor in cell E4 , and insert a row , the formula should change to :

=E6/COUNT(E1:E5)

Narayan
 
You must log in or register to reply here.
Back
Top