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 numbe
are anything from nothing to 10000. I want the hourly number to appea
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 b
$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.3
I48=(nothing)   G2 Should be (nothing)

7th Hour
D48 = $100.00 E48 = (nothing) F48 = $230.00 G48 = $56.00 H48 = $456.3
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.3
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.  (
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 :

--
flapoke
-----------------------------------------------------------------------
flapokey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2669
View this thread: http://www.excelforum.com/showthread.php?threadid=46681

0
9/12/2005 3:49:16 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
2676 Views

Similar Articles

[PageSpeed] 15

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?action=getinfo&userid=26690
> View this thread: http://www.excelforum.com/showthread.php?threadid=466819
> 
> 
0
BJ (832)
9/12/2005 6:41:07 PM
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.1v972d_1126541112.8291@excelforum-nospam.com>,
 flapokey <flapokey.1v972d_1126541112.8291@excelforum-nospam.com> 
 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 :(
0
domenic22 (716)
9/12/2005 6:54:58 PM
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 <domenic22-CA2C1B.14545812092005@msnews.microsoft.com>,
 Domenic <domenic22@sympatico.ca> 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.
0
domenic22 (716)
9/12/2005 7:00:47 PM
Hello,

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

--
flapoke
-----------------------------------------------------------------------
flapokey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2669
View this thread: http://www.excelforum.com/showthread.php?threadid=46681

0
9/13/2005 2:25:10 PM
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.1vayyk_1126623916.8627@excelforum-nospam.com>,
 flapokey <flapokey.1vayyk_1126623916.8627@excelforum-nospam.com> 
 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:
0
domenic22 (716)
9/14/2005 2:37:00 AM
Reply:

Similar Artilces:

Return cell based on another cell help!
Hi, I have a spreadsheet with a list of codes in column A, a Master list of codes in column C, and a Master description in D that is associated with the values in C. If a code exists in A, I would like to check if the same code exists in C. If so, I would like to populate column B with the description in D. Can anyone show me how to do this please? Try this in B1: =IF(ISNA(MATCH(A1,$C$1:$C$100,0)),"No Match",VLOOKUP(A1,$C$1:$D$100,2,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============...

Is there a way to attach a xslt template to a xsltargumentlist? please help!
in C#, I use the following way to do transform, the I assign my extension object to the xslArg, so in xsl, I can call functions in the extension object. Now, I want functions in the extension object to call template in the xsl, this way I can put some customized code into the template. I think it is reasonable, but I don;t know how to do it, anyone has some idea on this? Thanks. xslt.Transform(xmlDoc, xslArg, textHTML, null); david wrote: > in C#, I use the following way to do transform, the I assign my extension > object to the xslArg, so in xsl, I can call functions in the extens...

HELP: Assistance required with trying to calculate distances based on latitude and longitude
Hi All, I have been searching the net everywhere and have not been fruitful as yet. I did find formulas to determine distances but when i put them into an SQL statement they cause errors. it appears Access spits the dummy if you have a calculation that is too long. I first tried it in MS Query in Excel and it kept popping up a statement to the effect of an error, then i put it directly into Access and the colums results all show "#error". I constructed the calculation slowly, part by part and it was accepting the staged construction until towards the very end. I was attempti...

Please help! Displaying only rows with empty cells in a numeric field using Advanced Filter. Thanks
Folks I am learning how to use the Advanced Filter (Menus: Data, Filter, Advanced Filter). I can’t use Autofilter because in some cases I have to look for records that have to meet more than 2 conditions in the same field Some of the fields in the list that I am trying to filter have only numeric values. One of the conditions I am trying to define would allow me to filter the list exposing those rows in a numeric field that are blank (empty cells containing no data). I have tried using the following wildcard and formula combinations� <>* , =C11="" and <g … in the criter...

Using formulas to filter
Is there a way to filter a list by formulas the same way the Data->Filter option works? I'd like to take the matrix: Col A Col B Col C Col D Col E ===== ===== ===== ===== ===== Smith 30 $104.2 Yes 52 Jones 31 $155.3 No 51 Jones 31 $422.2 Yes 49 Freer 31 $424.3 Yes 42 Waylan 30 $322.5 No 50 Smith 31 $288.3 Yes 49 etc. And, using a formula, filter on Col B = 31 to produce: Col AA Col AB ...

Microsoft Excel Help
Why are they called arguments when creating an Excel function? i need to no for a Excel test! what makes them arguments? Hi if you mean the 'parameteres' of a function how would you call them otherwise :-) -- Regards Frank Kabel Frankfurt, Germany "martz" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0b2601c4d8e5$c05f7bd0$a301280a@phx.gbl... > Why are they called arguments when creating an Excel > function? i need to no for a Excel test! what makes them > arguments? ...

Need help grouping charts
I have identical charts which occur on multiple sheets. Is there any wa to group them for editing? When I group the sheets themselves, it seems to deactivate the charts I tried doing it with a macro as an alternative and then flipping th sheets one by one and keying the macro, but I get a message box with: "Run-time error 1004", "Unable to get the ChartObjects property of th worksheet class". Does anyone have any ideas -- Izz ----------------------------------------------------------------------- Izzy's Profile: http://www.excelforum.com/member.php?action=getin...

Help with Now()
Hi, I am creating a spreadsheet and I need to have a cell and that record the date and time each row is entered seperately. The problem I have at the moment is whenever I add a new row of data i updates the previous rows time? does any one have any suggestions on how to stop this happening? thank -- sydoll ----------------------------------------------------------------------- sydolly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2712 View this thread: http://www.excelforum.com/showthread.php?threadid=46697 Now() is a volatile function and will change ev...

DB Mail is is not working. Need help please.
I have try stop and start the db mail by executing the SPs below but no luck. Does anyone has seem the error below and how to fix it. Thanks in advance for your advise/help. --steps USE msdb; go EXECute sysmail_stop_sp; go EXECute sysmail_start_sp; go EXECute sysmail_help_queue_sp @queue_type = 'Mail'; go queue_type length state last_empty_rowset_time last_activated_time ---------- ----------- ---------------------------------------------------------------- ----------------------- ------------...

Help with Raw Stock Data Collection
Is it possible to have Excel search through 1 minute raw data and be able to determine if the price moves between a certain boundary for a certain time then it will be classified as "Type 1". And if it is increasing for a certain time then it will be "Type 2" and likewise for when it is decreasing. I want Excel to be able to automatically search through the raw data and determine what is the type of state without myself having to manually pinpoint it on the chart then going back and doing a manual search and calculation in Excel. Only issue is that the exact number determi...

Third request for help with Payables Error message!
Ok I have posted this for the third time with no response! I need help to know if there is any kind of issue on the back end of our environment. If someone could help it would be greatly appreciated! We are currently running GP 10 SP1 and apparently since version 8 (we did not upgrade to 9) there has been the following error message while launching a transaction out of Smartlist, when we go to inquiry for AP documents, and drill down of PMTRX & PMCHK from the JE inquiry view: Unhandeled Script Exception: Index 0 array of 'PM_Transaction_Inquiry_Fill' Script terminated We ran a ...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

What could keep formulas from recalculating?
I have a workbook that I received from a company with which I do business. On it are several worksheets (tabs). On one sheet, if I go to enter a formula in a cell, it displays it as text instead of calculating a value. For example, if I type =A1+A2 it will display that exact text in the cell as opposed to displaying the sum of cells A1 and A2. On other worksheets in the same workbook, entering formulas seems to work just fine. I have checked in Options, and auto-calc is checked (pressing F9 also has no effect). I can only assume that there is some setting of which I am unaware that is prev...

Calendar/Dates Help
Hi All, I have an excel spreadsheet that lists every date in the year, with a particular code in the next cell. IE: Monday 3/01/2005 11M 22M 32M Tuesday 4/01/2005 11T 22T 32T Wednes 5/01/2005 11W 22W 32W Thursday 6/01/2005 11H 22H 32H Friday 7/01/2005 11F 22F 32F Saturday 8/01/2005 11S 22S 32S Sunday 9/01/2005 11N 22N 32N Monday 10/01/2005 11M 21M 33M Tuesday 11/01/2005 11T 21T 33T Wednes 12/01/2005 11W 21W 33W Thursday 13/01/2005 11H 21H 33H Friday 14/01/2005 11F 21F 33F What I need is to be able to search by the code eg "33T" and have all the dates listed for ...

Urgent help with CStringArray
Hello, i need to return a CStringArray from a class method, but CStringArray (and CStringList also) doesn't have a copy constructor! I want avoid memory allocation within the metod because I don't want to force the class user to deallocate pointers... this is the piece of code: const CStringArray MyClass::MyMethod() { CStringArray StrArray; for (...) StrArray.Add("something"); return StrArray; // this will generate a missing copy constructor compile error } Thank you, Gaetano Sferra "Gaetano Sferra" <rebusmail@iol.it> wrote in messag...

vlookup help #3
hello, I cant figure out the proper syntax for the attached workbook. Th first 8 options on worksheet two work perfectly ( ie when checked, the appear on worksheet 3) I dont know how to apply the formula of th first 8 options to the rest of the checkboxes. Can someone please help me figure out how to do this? Thanks i advance. Aladdi Attachment filename: start order2.zip Download attachment: http://www.excelforum.com/attachment.php?postid=46674 -- Message posted from http://www.ExcelForum.com Right-click on the sheet tab for the Options worksheet. Select View ...

Bill Pay Help
I just signed up for Bill Pay (I have Money 2005), I'm not sure how to coordinate Bill Pay with my Bills list and my checking account register. It makes a separate Bill Pay register, but won't allow me to merge it with my checking account (as Help suggests). Tech support was completely useless, in fact it seems they're in a hurry to get us off the phone. ...

Just Help Me
Hello All . . . Please answer the questionary in the following URI http://nees.awardspace.com/ Thanks a lot . . . :D Post the question here, and we might. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CorfuVBProgrammer" <merianosnikos@gmail.com> wrote in message news:1167902152.017496.205590@11g2000cwr.googlegroups.com... > Hello All . . . > > Please answer the questionary in the following URI > > http://nees.awardspace.com/ > > Thanks a lot . . . :D > ...

Help revising slow code
I am using the following code to remove all the lines in a worksheet that have a zero value in Column H. Because there are thousands of rows in the worksheet, this loop takes forever! Does anyone have any suggestions for doing this more quickly? Thanks! Sub Delete_Row_w0() Dim Firstrow As Long Dim LastRow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With Sheets(1) .Select ...

Need Help
Query: Say i have multiple sheets dated 04/01/10 to 04/30/10, each contain names, say A, B, C, D, etc... but these name does not appear in the same order in each sheet. Now data is in time format in front of A, B, C, D, etc. Eg. in sheet 04/01/10 A1 B1 A 1:00:00 D 1:53:00 C 2:45:00 like this data in other sheet till 04/30/10. Now in a new sheet i want this data to be collated in time format in front of the individual sorted in Alphabetical order. Also it should give me the Sum of the data. Is it possible and how? Thanks in advance for the help!...

corrupted PST file
Hi, I have Outlook 98, running on XP, on a PII 400Mghz Dell desktop. My PST file is about 1.95GB. It has been running extremely slow, and has had to be restarted a bunch of times in the past 2 weeks. Yesterday, it gave an error that the PST file was corrupted, and that PST file need to have the Inbox Repair Tool run on the file, and then the system restarted. I ran the Inbox Repair Tool on the PST, and it did not work. I am totally unable to use the file, which has all my critical contacts and emails. If someone could help out, I would be MOST grateful.... I pulling my friggin ...

Autofilter help
Hi all I have 4 columns of data with an auto filter on - all ok I then reduce my selection so I finally only have one row of data - all ok Not ok: Problem: Is there an easy way that I can copy the data in the top row of the filter, to another worksheet page - so if the filter is changed so top line shows new result - still copies over the data in the top row of the filter. Bearing in mind that the top row could be I.E. Originally Row 8 or 108 depending on the selection. Autofilter Line = 1 2 3 4 eg Result of A/F a a a a <== copy the result of this line to dif...

HELP
I just wrote a check to the IRS, and in the Expense Catagory I indicated "Taxes : Federal Income Tax". Yet in the Income/Expense report, this payment is not included in my total federal taxes paid for this month. I should note that the check was written from an Investment Account. If I write the check from my checking account, the amount correctly shows up in the Income/Expense report. Any ideas as to why it works differently, depending on which account I pay the tax from? In microsoft.public.money, Tom wrote: >I just wrote a check to the IRS, and in the Expense Cat...

R-squared formula
I was just wondering if anybody knows the formula for the r-squared value for a power curve. Excel states that it uses a transformed r-squared value. I am trying to calculate the value by doing the mathematical calculations in the spreadsheet. I have gotten the correct equation that the chart shows, but I can't seem to get the correct r-squared value. The formulas that they give are: R^2 = 1-(SSE/SST) SSE = E(Yi-Yi^)^2 SST = (EYi^2)-(EYi)^2/n E is the best I can get to a sigma in this. Sigma is the sign to sum up all the indicated values. Yi is the original Y values. Yi^ ...