Extract numbers from cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Orf Bartrop - 16 Dec 2006 01:16 GMT

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?

Reply to this Message

Max - 16 Dec 2006 11:13 GMT

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))))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>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?

Reply to this Message

Orf Bartrop - 16 Dec 2006 23:37 GMT

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

>Assume data in sheet: Fill up Records
>in col L is within rows 1 - 100 (say)
[quoted text clipped - 6 lines]
>Records'!C1:C200,MAX(('Fill up Records'!C1:C200<>"")*(ROW(A1:A200))))
>

Reply to this Message

Max - 17 Dec 2006 01:04 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I could not get your formula to work. Max. It stated that: A value used in
>the formula is of the wrong data type.
[quoted text clipped - 17 lines]
>
> Orf

Reply to this Message

Orf Bartrop - 17 Dec 2006 04:13 GMT

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

>Both the numerator and denominator formulae are array formulae which require
>a special way of confirming the formula via pressing CTRL+SHIFT+ENTER
[quoted text clipped - 17 lines]
>Extr 2nd last num fr col L n div by last num in col C.xls
>

Reply to this Message

Max - 17 Dec 2006 08:04 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 3 lines]
>
> Orf

Reply to this Message