Excluding "function" cell from column calculation

Question

Hi! I have a column with values in column K (K1:K98). When I enter a function to calculate column K it does not output a value. I think the reason is that I am inputting the function in K99, which is not part of the data. How can I specifically exclude this cell (K99) from calculation. Is there any specific variable I can input with the formula to exclude K99.

Your help is much appreciated.

Answer 1

I want to use =AVERAGE and =XIRR functions.

The above solution you provided seems like something that might work for =AVERAGE, but I am getting value output of 0 with a circular reference error. My values are in percentage and there are blank cells in the middle too.

Answer 2

Ok, how about something like this:

=SUM(INDIRECT("K2:K" & ROW()))

The ROW() returns the row the formula is currently in so when in K99, you would get the equivalent of K2:K99

If you inserted a couple of rows above this, then it would become the equivalent of K2:K101

Answer 3

Hi,

What function do you have in cell K99 and what problem are you facing?

Answer 4

Thank you for your reply.

Yes that I understand. Thing is I constantly insert rows and with this technique I have to keep changing the formula.

Answer 5

I don't know what your function in K99 is, but let us say it is a simple SUM(), you could write it like this to exclude K99

=SUM(K2:K98)

that would total the values from row 2 through row 98 of column K.

If you then wanted to add (or perform other function) to values after K99, just start that reference at K100, maybe something like

=SUM(K2:K98) - SUM(K100:K150)