Conditional Entry

[Bailua]

G'day

How do i create a formula for just one cell based on the last entry in a
particula row or column? The last entry will obviously change every time i
enter a new value in that particaul row or column.

eg.
Date Value Formula
1/1 15 = 100 MINUS " last entry in value column" (ie.
equals 55 on 41, but will change to 66 on 5/1)
2/1 39
3/1 27
4/1 45
5/1 34
6/1
7/1


Cheers
B1.

 

[Biff]

Hi!

Try this:

=100-IF(COUNT(B:B),LOOKUP(9.99999999999999E+307,B:B),0)

Biff

 

[Mallycat]

This will do it for you

=100-INDIRECT(ADDRESS((COUNT(A:A)),1))

The ,1 at the end of the formula means Column A, change this to 2 for
B, 3 for C etc.

Matt

edit: improved using idea from the previous post

 

[Gord Dibben]

=100-LOOKUP(9.99999999999999E+307,F:F)

Will return 100 minus the last value in Column F

=100-LOOKUP(9.99999999999999E+307,3:3)

Will return 100 minus the last value in Row 3


Gord Dibben MS Excel MVP

 

[Biff]

=100-INDIRECT(ADDRESS((COUNT(A:A)),1))

Doesn't account for the possibilty (probabiltiy) of a header row. If Count =
1 = 100 - A1 = 100 - "Date"

Also doesn't account for the possibilty of empty cells within the range:
Count = 4 = 100 - A4 = 100 - 0

A1 = Date
A2 = 10
A3 = 22
A4
A5 = 15
A6
A7 = 44

Biff

 

[bobocat]

Amazing.
=lookup(9.99999999E+308, B:B) is to capture the last value in that column.
what if the last cell in that column is a text? can we capture that?

I tried the below formula,
{=indirect(address(max(if(isblank(a1:a65536),0,row(a1:a65536))),column(a1)))}
do you have any idea to create a shorter one?

 

[Biff]

For the last TEXT entry in a column:

=LOOKUP(REPT("z",255),A:A)

See this for more info:

http://xldynamic.com/source/xld.LastValue.html

Biff

 

[RagDyeR]

Try this for last text *or* numeric cell:

=LOOKUP(2,1/(1-ISBLANK(A1:A100)),A1:A100)

Try this *array* formula for row number of last data cell:

=MAX(IF(1-ISBLANK(A1:A100),ROW(A1:A100)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


G'day

How do i create a formula for just one cell based on the last entry in a
particula row or column? The last entry will obviously change every time i
enter a new value in that particaul row or column.

eg.
Date Value Formula
1/1 15 = 100 MINUS " last entry in value column" (ie.
equals 55 on 41, but will change to 66 on 5/1)
2/1 39
3/1 27
4/1 45
5/1 34
6/1
7/1


Cheers
B1.

 

[Gord Dibben]

For text you could use =LOOKUP(REPT("z",255),B2:B10000)

But RD's method covers both numerics and text so would be more versatile.


Gord

Amazing.
=lookup(9.99999999E+308, B:B) is to capture the last value in that column.
what if the last cell in that column is a text? can we capture that?

I tried the below formula,
{=indirect(address(max(if(isblank(a1:a65536),0,row(a1:a65536))),column(a1)))}
do you have any idea to create a shorter one?

Gord Dibben MS Excel MVP

 

[Bobocat]

Thank you.

For the last TEXT entry in a column:

=LOOKUP(REPT("z",255),A:A)

See this for more info:

http://xldynamic.com/source/xld.LastValue.html

Biff

 

[Biff]

You're welcome!

Biff

Thank you.

 

[Bailua]

G'day Biff

Thanks bro, that is awesome. I have absolutely no idea how that works, but
it does.

Absolute brilliance

Cheers

B1.