I need a formula: 25 divided by the last number populated in columns
C14:C43, F5:F35, I5:I34. C14 will be the first cell populated, I34 the last
populated.
--
ph

Hi Phil,

Where should the results appear ?

Carim

In cell N3
--
ph


"Carim" wrote:

Hi Phil,

Where should the results appear ?

Carim

"Phil Hageman" skrev i en meddelelse
...
I need a formula: 25 divided by the last number populated in columns
C14:C43, F5:F35, I5:I34. C14 will be the first cell populated, I34 the
last
populated.
--
ph

What exactly do you mean by "last number"?
With data in C14:C23 (C24:C43 blank), F5:F20 (F21:F35 blank),
i5:i22 (i23:i34 blank), which cell holds the last number?
With data in C14:C34, F5:F34, i5:i30 (blanks as above), which
cell holds the last number?


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Problem restated:

Users have three columns (months) to post data: C14:C43, F5:F35, and
I5:I34. They post sequentially, starting in cell C14, posting in C15 next,
then C16, etc. After they post in C43 (the last entry for that month), they
move to the next column and post in F5, then F6, etc. Then the move to the
third column and post. The desired formula is this:

($D$4 - LP)
------------------ Where LP is the last posting in the three monthly
columns.
($D$4 - $D$5)


"Leo Heuser" wrote:

"Phil Hageman" skrev i en meddelelse
...
I need a formula: 25 divided by the last number populated in columns
C14:C43, F5:F35, I5:I34. C14 will be the first cell populated, I34 the
last
populated.
--
ph

What exactly do you mean by "last number"?
With data in C14:C23 (C24:C43 blank), F5:F20 (F21:F35 blank),
i5:i22 (i23:i34 blank), which cell holds the last number?
With data in C14:C34, F5:F34, i5:i30 (blanks as above), which
cell holds the last number?


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Phil,

If I understand what you are looking for ...

Say for data of 1st month in range C14:C43, the formula to spot the
last post would be :

LastPost1 =OFFSET(C14,COUNT(C15:C43),0)
and you could go with D4 - LastPost1

But you would need to adjust the formula for each of your other 2
months :
LastPost2 =OFFSET(F5,COUNT(F6:F35),0)
and then D4 - LastPost2

LastPost3 =OFFSET(I5,COUNT(I6:I34),0)
and then D4 - LastPost3

HTH

Cheers
Carim

Thanks Carim. Is there a way in VB in a module where we could do this
without having to go in and adjust formulas? Eventually, this model will be
expanded to a one-year format.
--
ph


"Carim" wrote:

Hi Phil,

If I understand what you are looking for ...

Say for data of 1st month in range C14:C43, the formula to spot the
last post would be :

LastPost1 =OFFSET(C14,COUNT(C15:C43),0)
and you could go with D4 - LastPost1

But you would need to adjust the formula for each of your other 2
months :
LastPost2 =OFFSET(F5,COUNT(F6:F35),0)
and then D4 - LastPost2

LastPost3 =OFFSET(I5,COUNT(I6:I34),0)
and then D4 - LastPost3

HTH

Cheers
Carim

Hi Phil,

Yes a VB solution is always feasible ...
But
1.where do you want the results to show up ?
2.are you looking for an event macro ?

Carim

Hi Phil

=LOOKUP(9.99999999999999E+307,C14:C43)
will return the last value entered in the range
Place this in a cell e.g. A1
Repeat in B1 and C1 changing ranges as appropriate for your other two
columns.
If there is no data in any of the ranges, the formula will return #N/A
then the value of LP to substitute in your formula will be

=IF(NOT(ISNA(C1)),C1,IF(NOT(ISNA(B1)),B1,IF(NOT(IS NA(A1)),A1,0)))

P.S. What's the weather like in Naples? It's a lot cooler here in the
UK than when you were here in July.

--
Regards

Roger Govier


"Phil Hageman" wrote in message
...

Problem restated:

Users have three columns (months) to post data: C14:C43, F5:F35, and
I5:I34. They post sequentially, starting in cell C14, posting in C15
next,
then C16, etc. After they post in C43 (the last entry for that
month), they
move to the next column and post in F5, then F6, etc. Then the move
to the
third column and post. The desired formula is this:

($D$4 - LP)
------------------ Where LP is the last posting in the three
monthly
columns.
($D$4 - $D$5)


"Leo Heuser" wrote:

"Phil Hageman" skrev i en
meddelelse
...
I need a formula: 25 divided by the last number populated in
columns
C14:C43, F5:F35, I5:I34. C14 will be the first cell populated, I34
the
last
populated.
--
ph

What exactly do you mean by "last number"?
With data in C14:C23 (C24:C43 blank), F5:F20 (F21:F35 blank),
i5:i22 (i23:i34 blank), which cell holds the last number?
With data in C14:C34, F5:F34, i5:i30 (blanks as above), which
cell holds the last number?


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Rodger,

Send me an e-mail at :

Phil
--
ph


"Roger Govier" wrote:

Hi Phil

=LOOKUP(9.99999999999999E+307,C14:C43)
will return the last value entered in the range
Place this in a cell e.g. A1
Repeat in B1 and C1 changing ranges as appropriate for your other two
columns.
If there is no data in any of the ranges, the formula will return #N/A
then the value of LP to substitute in your formula will be

=IF(NOT(ISNA(C1)),C1,IF(NOT(ISNA(B1)),B1,IF(NOT(IS NA(A1)),A1,0)))

P.S. What's the weather like in Naples? It's a lot cooler here in the
UK than when you were here in July.

--
Regards

Roger Govier


"Phil Hageman" wrote in message
...

Problem restated:

Users have three columns (months) to post data: C14:C43, F5:F35, and
I5:I34. They post sequentially, starting in cell C14, posting in C15
next,
then C16, etc. After they post in C43 (the last entry for that
month), they
move to the next column and post in F5, then F6, etc. Then the move
to the
third column and post. The desired formula is this:

($D$4 - LP)
------------------ Where LP is the last posting in the three
monthly
columns.
($D$4 - $D$5)


"Leo Heuser" wrote:

"Phil Hageman" skrev i en
meddelelse
...
I need a formula: 25 divided by the last number populated in
columns
C14:C43, F5:F35, I5:I34. C14 will be the first cell populated, I34
the
last
populated.
--
ph

What exactly do you mean by "last number"?
With data in C14:C23 (C24:C43 blank), F5:F20 (F21:F35 blank),
i5:i22 (i23:i34 blank), which cell holds the last number?
With data in C14:C34, F5:F34, i5:i30 (blanks as above), which
cell holds the last number?


--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Phil Hageman" skrev i en meddelelse
...

Problem restated:

Users have three columns (months) to post data: C14:C43, F5:F35, and
I5:I34. They post sequentially, starting in cell C14, posting in C15
next,
then C16, etc. After they post in C43 (the last entry for that month),
they
move to the next column and post in F5, then F6, etc. Then the move to
the
third column and post. The desired formula is this:

($D$4 - LP)
------------------ Where LP is the last posting in the three monthly
columns.
($D$4 - $D$5)



Hi Phil

Here is a UDF (User Defined Function) to solve your problem.

1. Select all cells in the 3 ranges (select the first range,
press <Ctrl and hold it, select the second range,
select the third range, release <Ctrl
2. Click in the name box (at the extreme left of the formula bar)
and enter e.g. "Block" without quotes. <Return

The data cells are now named "Block"

3. Enter (or copy) the code below in a general module.


Function LastPost(Months As Range) As Double
'Leo Heuser, 3 Sept. 2006
Dim Cell As Range
Dim CountCells As Long
Dim DummyArray() As Double
Dim EmptyFound As Boolean

LastPost = 0

If IsEmpty(Months.Cells(1, 1)) Then Exit Function

ReDim DummyArray(1 To Months.Cells.Count)

For Each Cell In Months.Cells
CountCells = CountCells + 1
DummyArray(CountCells) = Cell.Value
If IsEmpty(Cell) Then
EmptyFound = True
LastPost = DummyArray(CountCells - 1)
Exit For
End If
Next Cell

If Not EmptyFound Then LastPost = DummyArray(CountCells)
End Function


4. In N3 in the worksheet enter this formula:
=(d4-lastpost(block))/(d4-d5)
(casing doesn't matter)

To expand "Block" either go to Insert Name Define and
make a manual adjustment or delete "Block" in
Insert Name Define and make "Block" by following steps 1 and 2.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Leo,

Thanks for your solution. This works for the first column range. The
formula in Cell N3 calling for the function, works too, in the same way, for
the first column. Numbers posted in the second or third column do not
participate in the code/calculation. "Block" is set up properly. I copied
your code directly - did not alter it in any way.

Phil
--
ph


"Leo Heuser" wrote:

"Phil Hageman" skrev i en meddelelse
...

Problem restated:

Users have three columns (months) to post data: C14:C43, F5:F35, and
I5:I34. They post sequentially, starting in cell C14, posting in C15
next,
then C16, etc. After they post in C43 (the last entry for that month),
they
move to the next column and post in F5, then F6, etc. Then the move to
the
third column and post. The desired formula is this:

($D$4 - LP)
------------------ Where LP is the last posting in the three monthly
columns.
($D$4 - $D$5)



Hi Phil

Here is a UDF (User Defined Function) to solve your problem.

1. Select all cells in the 3 ranges (select the first range,
press <Ctrl and hold it, select the second range,
select the third range, release <Ctrl
2. Click in the name box (at the extreme left of the formula bar)
and enter e.g. "Block" without quotes. <Return

The data cells are now named "Block"

3. Enter (or copy) the code below in a general module.


Function LastPost(Months As Range) As Double
'Leo Heuser, 3 Sept. 2006
Dim Cell As Range
Dim CountCells As Long
Dim DummyArray() As Double
Dim EmptyFound As Boolean

LastPost = 0

If IsEmpty(Months.Cells(1, 1)) Then Exit Function

ReDim DummyArray(1 To Months.Cells.Count)

For Each Cell In Months.Cells
CountCells = CountCells + 1
DummyArray(CountCells) = Cell.Value
If IsEmpty(Cell) Then
EmptyFound = True
LastPost = DummyArray(CountCells - 1)
Exit For
End If
Next Cell

If Not EmptyFound Then LastPost = DummyArray(CountCells)
End Function


4. In N3 in the worksheet enter this formula:
=(d4-lastpost(block))/(d4-d5)
(casing doesn't matter)

To expand "Block" either go to Insert Name Define and
make a manual adjustment or delete "Block" in
Insert Name Define and make "Block" by following steps 1 and 2.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Phil

And the first column range is full before you start entering
numbers in the second etc.?

It's tested and found working in Excel 2003.

You are welcome to attach a copy of your workbook
to a personal mail, and I'll take a look at it. Please
zip it, if its a large file.

leo-heuser at adslhome.dk

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Phil Hageman" skrev i en meddelelse
...
Hi Leo,

Thanks for your solution. This works for the first column range. The
formula in Cell N3 calling for the function, works too, in the same way,
for
the first column. Numbers posted in the second or third column do not
participate in the code/calculation. "Block" is set up properly. I
copied
your code directly - did not alter it in any way.

Phil
--
ph


"Leo Heuser" wrote:

"Phil Hageman" skrev i en
meddelelse
...

Problem restated:

Users have three columns (months) to post data: C14:C43, F5:F35, and
I5:I34. They post sequentially, starting in cell C14, posting in C15
next,
then C16, etc. After they post in C43 (the last entry for that month),
they
move to the next column and post in F5, then F6, etc. Then the move to
the
third column and post. The desired formula is this:

($D$4 - LP)
------------------ Where LP is the last posting in the three monthly
columns.
($D$4 - $D$5)



Hi Phil

Here is a UDF (User Defined Function) to solve your problem.

1. Select all cells in the 3 ranges (select the first range,
press <Ctrl and hold it, select the second range,
select the third range, release <Ctrl
2. Click in the name box (at the extreme left of the formula bar)
and enter e.g. "Block" without quotes. <Return

The data cells are now named "Block"

3. Enter (or copy) the code below in a general module.


Function LastPost(Months As Range) As Double
'Leo Heuser, 3 Sept. 2006
Dim Cell As Range
Dim CountCells As Long
Dim DummyArray() As Double
Dim EmptyFound As Boolean

LastPost = 0

If IsEmpty(Months.Cells(1, 1)) Then Exit Function

ReDim DummyArray(1 To Months.Cells.Count)

For Each Cell In Months.Cells
CountCells = CountCells + 1
DummyArray(CountCells) = Cell.Value
If IsEmpty(Cell) Then
EmptyFound = True
LastPost = DummyArray(CountCells - 1)
Exit For
End If
Next Cell

If Not EmptyFound Then LastPost = DummyArray(CountCells)
End Function


4. In N3 in the worksheet enter this formula:
=(d4-lastpost(block))/(d4-d5)
(casing doesn't matter)

To expand "Block" either go to Insert Name Define and
make a manual adjustment or delete "Block" in
Insert Name Define and make "Block" by following steps 1 and 2.

--
Best regards
Leo Heuser

Followup to newsgroup only please.