Division Formula
 

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

Division Formula
 

Hi Phil,

Where should the results appear ?

Carim

Division Formula
 

In cell N3
--
ph


"Carim" wrote:

Hi Phil,

Where should the results appear ?

Carim

Division Formula
 

"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.

Division Formula
 

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.

Division Formula
 

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

Division Formula
 

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.

Division Formula
 

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

Division Formula
 

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

Division Formula
 

Carim,

The solution to the macro woul appear on the worksheet where posting is done.

I guess the event would be any time a number is posted in any of the three
ranges.

Phil
--
ph


"Carim" wrote:

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

Division Formula
 

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.

Division Formula
 

"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.

Division Formula
 

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.

Division Formula
 

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.

Division Formula
 

Hi Leo,

Okay. It works as you intended. I was testing incorrectly. I simply jumped
to the third column and entered data, expecting the my result. When I filled
in the first column and posted to the second, it worked perfectly. Thank you
very much for your time and a great solution to my problem.

Phil
--
ph


"Leo Heuser" wrote:

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.

Division Formula
 

Hi Phil

I'm glad, you got it to work.
Thanks for the feedback!

I could make a variant of the function, if you're interested,
where a #NUM! error is returned, if there is a blank cell
above entered data. This would prevent incorrect data
to be used in the formula in N3, if the user by accident leaves
a blank cell behind.

Leo Heuser


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

Okay. It works as you intended. I was testing incorrectly. I simply
jumped
to the third column and entered data, expecting the my result. When I
filled
in the first column and posted to the second, it worked perfectly. Thank
you
very much for your time and a great solution to my problem.

Phil
--
ph


"Leo Heuser" wrote:

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.