I have 2 columns of numbers in a sheet called "Fill up Records". I wish
to copy the data in the 2nd to last cell before a blank in one column (
L) and the last number before a blank in the other column (C) and divide
the first by the second and put the answer on a different sheet. The
blank cells only occur after the first blank cell.
What formula would I use in the second sheet to do this?

Assume data in sheet: Fill up Records
in col L is within rows 1 - 100 (say)
in col C is within rows 1 - 200 (say)

Then in the other sheet,
Try this expression, array-entered (press CTRL+SHIFT+ENTER):
=INDEX('Fill up Records'!L1:L100,MAX(('Fill up
Records'!L1:L100<"")*(ROW(A1:A100)))-1)/INDEX('Fill up
Records'!C1:C200,MAX(('Fill up Records'!C1:C200<"")*(ROW(A1:A200))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Orf Bartrop" wrote in message
...
I have 2 columns of numbers in a sheet called "Fill up Records". I wish to
copy the data in the 2nd to last cell before a blank in one column ( L) and
the last number before a blank in the other column (C) and divide the first
by the second and put the answer on a different sheet. The blank cells only
occur after the first blank cell.
What formula would I use in the second sheet to do this?

I could not get your formula to work. Max. It stated that: A value used
in the formula is of the wrong data type.
I tried fiddling with the formula but to no avail.

Column C contains a number entered by the keyboard and column L contains
a formula that calculates a monetary value.

The row numbers for all columns I need to scan is 7 - 999. I change the
references in your formula to these values.

I looked up the Help on INDEX and MAX but had difficulty in
understanding the directions. Nor could I understand the * sign in your
formula. I thought the * was for multiplication. I tried changing it to
a comma but it still didn't work.

I could not understand the reason for using the CTRL+SHIFT+ENTER. Why
can't I just paste the formula into the formula bar?

Thanks for your help,

Orf

Max wrote:

Assume data in sheet: Fill up Records
in col L is within rows 1 - 100 (say)
in col C is within rows 1 - 200 (say)

Then in the other sheet,
Try this expression, array-entered (press CTRL+SHIFT+ENTER):
=INDEX('Fill up Records'!L1:L100,MAX(('Fill up
Records'!L1:L100<"")*(ROW(A1:A100)))-1)/INDEX('Fill up
Records'!C1:C200,MAX(('Fill up Records'!C1:C200<"")*(ROW(A1:A200))))

Both the numerator and denominator formulae are array formulae which require
a special way of confirming the formula via pressing CTRL+SHIFT+ENTER
(instead of the usual way of just pressing ENTER). Correctly array-entered,
Excel will auto-wrap curly braces: { } around the formula. Look out for
these curly braces in the formula bar as a visual check that the formula is
correctly array-entered.

Adjusting the cell references pointing to cols L and C in the source sheet:
Fill up Records to suit your actual ranges, the array formula would now be:

=INDEX('Fill up Records'!L7:L999,MAX(('Fill up
Records'!L7:L999<"")*(ROW(A1:A993)))-1)/INDEX('Fill up
Records'!C7:C999,MAX(('Fill up Records'!C7:C999<"")*(ROW(A1:A993))))

Note: The range in ROW(A1:A993) is an equivalent sized range to L7:L999.
This term ROW(A1:A993) always starts from row 1.

Here's a working sample for the above to illustrate:
http://www.savefile.com/files/344647
Extr 2nd last num fr col L n div by last num in col C.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Orf Bartrop" wrote in message
...
I could not get your formula to work. Max. It stated that: A value used in
the formula is of the wrong data type.
I tried fiddling with the formula but to no avail.

Column C contains a number entered by the keyboard and column L contains a
formula that calculates a monetary value.

The row numbers for all columns I need to scan is 7 - 999. I change the
references in your formula to these values.

I looked up the Help on INDEX and MAX but had difficulty in understanding
the directions. Nor could I understand the * sign in your formula. I
thought the * was for multiplication. I tried changing it to a comma but
it still didn't work.

I could not understand the reason for using the CTRL+SHIFT+ENTER. Why
can't I just paste the formula into the formula bar?

Thanks for your help,

Orf

Thank you Max, I was using the CTRL+SHIFT+ENTER incorrectly. Seeing the
answer I want is in cents I put a *100 on the end and got the answer I
wanted.
I downloaded your example and it did help me to understand (partially)
what was occurring.

Orf

Max wrote:

Both the numerator and denominator formulae are array formulae which require
a special way of confirming the formula via pressing CTRL+SHIFT+ENTER
(instead of the usual way of just pressing ENTER). Correctly array-entered,
Excel will auto-wrap curly braces: { } around the formula. Look out for
these curly braces in the formula bar as a visual check that the formula is
correctly array-entered.

Adjusting the cell references pointing to cols L and C in the source sheet:
Fill up Records to suit your actual ranges, the array formula would now be:

=INDEX('Fill up Records'!L7:L999,MAX(('Fill up
Records'!L7:L999<"")*(ROW(A1:A993)))-1)/INDEX('Fill up
Records'!C7:C999,MAX(('Fill up Records'!C7:C999<"")*(ROW(A1:A993))))

Note: The range in ROW(A1:A993) is an equivalent sized range to L7:L999.
This term ROW(A1:A993) always starts from row 1.

Here's a working sample for the above to illustrate:
http://www.savefile.com/files/344647
Extr 2nd last num fr col L n div by last num in col C.xls

.. I was using the CTRL+SHIFT+ENTER incorrectly
Ah, it's a typical error in our haste to get array formulas going <g
(happens to me, too)

Glad to hear the suggestion gave what you wanted, Orf.
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Orf Bartrop" wrote in message
...
Thank you Max, I was using the CTRL+SHIFT+ENTER incorrectly. Seeing the
answer I want is in cents I put a *100 on the end and got the answer I
wanted.
I downloaded your example and it did help me to understand (partially)
what was occurring.

Orf