HELP !!! I have a ARRAY Formula HELP !!!
 

Hello,

Here is the ARRAY Formula I have and this is what I am using it for.
The situation is that it worked 1 time and than not again.

=INDEX(D48:K48,,MAX(IF(D48:K48<"",COLUMN(D48:K48) ))-COLUMN(D48)+1


Duty:

I have a row of number that appear hourly (DOLLAR AMOUNTS), the number
are anything from nothing to 10000. I want the hourly number to appear
in specified cell. Here is an example. (I am using EXCEL 2000)

Row D48:K48 answer in cell G2


1st hour
D48 = $100.00 G2 Should be $100.00

2nd Hour
D48 = $100.00 E48 = (nothing) G2 Should be (nothing)

3rd Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G2 Should be $230.00

4th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 G2 Should be
$56.00

5th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
G2 Should be $456.34

6th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) G2 Should be (nothing)

7th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) J48=$789.52 G2 Should be $789.52

8th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) J48=$789.52 K48= $45.67 G2 Should be $45.67

As I said this array formula worked 1 or 2 times and than nothing. (I
did do the cntrl+shift+enter)

What shows in G2 now is Blank the cell is blank, nothing

I have tried to retype it and cntrl+shift+enter. Nothing !!!

Can someone help me

flapokey :(


--
flapokey
------------------------------------------------------------------------
flapokey's Profile: http://www.excelforum.com/member.php...o&userid=26690
View this thread: http://www.excelforum.com/showthread...hreadid=466819

the trouble,I think, is that the original d48:K48 is being treated as an
array rather than a range.
Try
=OFFSET(D48,,MAX(IF(D48:K48<"",COLUMN(D48:K48),0)-COLUMN(D48)))

Question if there is nothing in E48 for the second hour, how can there be
nothing as a response. A "0" yes but nothing no
"flapokey" wrote:


Hello,

Here is the ARRAY Formula I have and this is what I am using it for.
The situation is that it worked 1 time and than not again.

=INDEX(D48:K48,,MAX(IF(D48:K48<"",COLUMN(D48:K48) ))-COLUMN(D48)+1


Duty:

I have a row of number that appear hourly (DOLLAR AMOUNTS), the number
are anything from nothing to 10000. I want the hourly number to appear
in specified cell. Here is an example. (I am using EXCEL 2000)

Row D48:K48 answer in cell G2


1st hour
D48 = $100.00 G2 Should be $100.00

2nd Hour
D48 = $100.00 E48 = (nothing) G2 Should be (nothing)

3rd Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G2 Should be $230.00

4th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 G2 Should be
$56.00

5th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
G2 Should be $456.34

6th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) G2 Should be (nothing)

7th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) J48=$789.52 G2 Should be $789.52

8th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) J48=$789.52 K48= $45.67 G2 Should be $45.67

As I said this array formula worked 1 or 2 times and than nothing. (I
did do the cntrl+shift+enter)

What shows in G2 now is Blank the cell is blank, nothing

I have tried to retype it and cntrl+shift+enter. Nothing !!!

Can someone help me

flapokey :(


--
flapokey
------------------------------------------------------------------------
flapokey's Profile: http://www.excelforum.com/member.php...o&userid=26690
View this thread: http://www.excelforum.com/showthread...hreadid=466819

First, your formula should be as follows...

=INDEX(D48:K48,,MAX(IF(D48:K48<"",COLUMN(D48:K48)-COLUMN(D48)+1)))

....confirmed with CONTROL+SHIFT+ENTER. Alternatively, you can use the
following formula instead which is confirmed with just ENTER...

=LOOKUP(9.99999999999999E+307,D48:IV48)

However, both formulas will return $100 for the second hour. If for the
second hour E48 actually contains a formula blank "", the following
formula will return the formula blank...

=LOOKUP(2,1/(1-ISBLANK(D48:IV48)),D48:IV48)

If E48 is actually empty and is not blank as a result of a formula, an
alternate solution would be required.

In article ,
flapokey
wrote:

Hello,

Here is the ARRAY Formula I have and this is what I am using it for.
The situation is that it worked 1 time and than not again.

=INDEX(D48:K48,,MAX(IF(D48:K48<"",COLUMN(D48:K48) ))-COLUMN(D48)+1


Duty:

I have a row of number that appear hourly (DOLLAR AMOUNTS), the number
are anything from nothing to 10000. I want the hourly number to appear
in specified cell. Here is an example. (I am using EXCEL 2000)

Row D48:K48 answer in cell G2


1st hour
D48 = $100.00 G2 Should be $100.00

2nd Hour
D48 = $100.00 E48 = (nothing) G2 Should be (nothing)

3rd Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G2 Should be $230.00

4th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 G2 Should be
$56.00

5th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
G2 Should be $456.34

6th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) G2 Should be (nothing)

7th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) J48=$789.52 G2 Should be $789.52

8th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.34
I48=(nothing) J48=$789.52 K48= $45.67 G2 Should be $45.67

As I said this array formula worked 1 or 2 times and than nothing. (I
did do the cntrl+shift+enter)

What shows in G2 now is Blank the cell is blank, nothing

I have tried to retype it and cntrl+shift+enter. Nothing !!!

Can someone help me

flapokey :(

Make that...

=LOOKUP(9.99999999999999E+307,D48:K48)

and

=LOOKUP(2,1/(1-ISBLANK(D48:K48)),D48:K48)

Notice that I've changed the ranges for both formulas to match your
original formula.

Hope this helps!

In article ,
Domenic wrote:

First, your formula should be as follows...

=INDEX(D48:K48,,MAX(IF(D48:K48<"",COLUMN(D48:K48)-COLUMN(D48)+1)))

...confirmed with CONTROL+SHIFT+ENTER. Alternatively, you can use the
following formula instead which is confirmed with just ENTER...

=LOOKUP(9.99999999999999E+307,D48:IV48)

However, both formulas will return $100 for the second hour. If for the
second hour E48 actually contains a formula blank "", the following
formula will return the formula blank...

=LOOKUP(2,1/(1-ISBLANK(D48:IV48)),D48:IV48)

If E48 is actually empty and is not blank as a result of a formula, an
alternate solution would be required.

Hello,

Thank you for all the help. But I am still getting nothing in G2 to
appear. I tried all the formulas. I am not using a Lookup table. If
you could futher help me I would be greatful. Thanks flapokey :cool:


--
flapokey
------------------------------------------------------------------------
flapokey's Profile: http://www.excelforum.com/member.php...o&userid=26690
View this thread: http://www.excelforum.com/showthread...hreadid=466819

How about entering 0 for those cells that contain 'nothing'. This way
you can use the following formula...

=LOOKUP(9.99999999999999E+307,D48:K48)

And, if you want to hide zero values, you can custom format your cells
as follows...

1) Select/highlight your range of cells

2) Format Cells Number Custom Type: 0;-0;;@

Would this work for you?

In article ,
flapokey
wrote:

Hello,

Thank you for all the help. But I am still getting nothing in G2 to
appear. I tried all the formulas. I am not using a Lookup table. If
you could futher help me I would be greatful. Thanks flapokey :cool: