MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Array Formula Problem - Automatic updating


Posted by Jim Hoover on February 16, 2001 8:41 AM

I have a complex array problem that I need to solve.
I created an array formula a couple of months ago
and I need to replicate what I did, but can't
remember what I did. Here is what the formula does:

ColumnA ColumnB ColumnC
Row1 12 6 =A1/B1
Row2 5 2 =A2/B2
Row3

What the formula does is check to make sure that
an entry is in ColumnA and ColumnB. As soon as
a user enters the data into Columns A&B, the
formula "appears" in Column C. The formula
that appears is exactly as I have portrayed above
and it of course performs the calculation in the
formula as the result.

This may seem simplistic, but I have the range in
Column C dynamically linked to a chart which is
updated when another entry is in Column C. I set
the spreadsheet up this way so that any user
could enter the new weekly data, and everything
else would occur automatically. Regretfully, I
can't remember what I did to create it.

Other pieces of information...
1) I don't have any named ranges performing the calculation
2) I do not have a macro or VBA macro that performs
the calculation
3) There are no entries or formulas in column C past
the last row that has data entered in ColA & ColB.

If you can help, I sure would appreciate it.
Sincerely,
Jim


Posted by Mark W. on February 16, 2001 1:26 PM

Could it be

{=IF(AND(ISNUMBER(A1:B1)),A1/B1,"")}

Posted by Jim Hoover on February 20, 2001 12:43 PM

That doesn't work because it still requires
that this formula be copied down in column
C. Col C is currently empty (no formula in
the background) until entries are made in
Col A and Col B.

thanks, but I still need help.