Trying To Reference The Last Cell In A Column That Contains Data

[gerryger]

I am trying to reference the last cell in a column that contains data. I am using the following formula:

=INDEX(N24:N227,MATCH(REPT("z",255),N24:N227))

The above formula works fine for column "N" because the entire column is empty to begin with. I eventually manually enter data it in.

The problem is when I apply the same formula to column "O" I get errors. The difference between column "N" and column "O" is even though there is no data in column "O" yet, there are formulas in every cell. The above formula does not seem to work if there formulas in the cells. It treats the entire column as being populated.

 

[graemekoz]

This is great info!

I did have a question though, this formula works for when you are returning the same value from a particular cell in a range. Is there a way of doing it so that it returns a result from a formula

For example:

When you enter a value in a particular cell, I want it to multiply by the value by what is in an adjacent cell, to generate a result.

A B E
1 10% 25
2 15% 40
3

So when there is no value in B2, cell E1 will return 25*10%. WHen a value is entered into B2, cell E1 wil return 40*15%.

Any ideas?

Graeme

 

[graemekoz]

This is great info!

I did have a question though, this formula works for when you are returning the same value from a particular cell in a range. Is there a way of doing it so that it returns a result from a formula

For example:

When you enter a value in a particular cell, I want it to multiply by the value by what is in an adjacent cell, to generate a result.

A B C
1 10% 25
2 15% 40
3


So when there is no value in B2, cell C1 will return B1*A1 (25*10%). WHen a value is entered into B2, cell C1 wil return A2*B2 (40*15%).

Any ideas?

Graeme

 

[T. Valko]

This is great info!

I did have a question though, this formula works for when you are returning the same value from a particular cell in a range. Is there a way of doing it so that it returns a result from a formula

For example:

When you enter a value in a particular cell, I want it to multiply by the value by what is in an adjacent cell, to generate a result.

A B C
1 10% 25
2 15% 40
3


So when there is no value in B2, cell C1 will return B1*A1 (25*10%). WHen a value is entered into B2, cell C1 wil return A2*B2 (40*15%).

Any ideas?

Graeme

I'm not sure what you're asking.

If you want the LAST entry in column B to be multiplied by the LAST entry in column C...

=PRODUCT(INDEX(B:C,MATCH(1E100,B:B),0))

 

[graemekoz]

hi T. Valko.

Thank you for your reply. It's actually very close to what I want and I realised I'd made a mistake in my question but again not sure how to fix that.

It's all about estimating a final crowd size.

At a particular time, the figure which I enter (into say Column B) might refer to say, 10% of the crowd in the venue (which is in column A).

For example at 5:00pm, I may have 1000 people in my venue and based on historical data, that represents 25% of the anticipated total attendance. Therefore in Cell C1, this would then spit out an ANTICIPATED ATTENDANCE of 4,000 (being 1000/25%).

Then at 5:15pm, I may have 2000 people in my venue and at that time it represents 33% of my anticipated attendance. By entering the 2,000 it then changes the ANTICIPATED ATTENDANCE in Cell 1 to 6,000 (being 2000/33%)

DOes that make sense? I'm trying to play with the formulae however it doesnt' seem to be working for me!!

Thanks in anticipation!!!

Graeme

 

[T. Valko]

hi T. Valko.

Thank you for your reply. It's actually very close to what I want and I realised I'd made a mistake in my question but again not sure how to fix that.

It's all about estimating a final crowd size.

At a particular time, the figure which I enter (into say Column B) might refer to say, 10% of the crowd in the venue (which is in column A).

For example at 5:00pm, I may have 1000 people in my venue and based on historical data, that represents 25% of the anticipated total attendance. Therefore in Cell C1, this would then spit out an ANTICIPATED ATTENDANCE of 4,000 (being 1000/25%).

Then at 5:15pm, I may have 2000 people in my venue and at that time it represents 33% of my anticipated attendance. By entering the 2,000 it then changes the ANTICIPATED ATTENDANCE in Cell 1 to 6,000 (being 2000/33%)

DOes that make sense? I'm trying to play with the formulae however it doesnt' seem to be working for me!!

Thanks in anticipation!!!

Graeme

Try this...

=LOOKUP(1E100,A:A)/LOOKUP(1E100,B:B)