Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 :( |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 -- flapokey ------------------------------------------------------------------------ flapokey's Profile: http://www.excelforum.com/member.php...o&userid=26690 View this thread: http://www.excelforum.com/showthread...hreadid=466819 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula | Excel Discussion (Misc queries) | |||
#VALUE! On An Array Formula Referencing a Range Outside The Workbo | Excel Discussion (Misc queries) | |||
problem with Array Formula | Excel Worksheet Functions | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |