Extract numbers from cells

  • Thread starter Thread starter Orf Bartrop
  • Start date Start date
O

Orf Bartrop

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))))
 
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
 
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
 
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
 
.. 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
 
Back
Top