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
2613 Views

Similar Articles

[PageSpeed] 0

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:

Formula #27
I am created a table containing weekly total of overtime for a number of employees (input table tab). I now want to create a chart (weekly overtime tab) for the weekly overtime. I want to be able to enter a date for a specific week and only create that week's chart. Is there a function that can lookup a date in the input table and use the numbers in the below rows to create the chart? Any information is appreciated. Thanks in advance Yes, you could do this, but you need to supply a bit more detail. What does your overtime sheet look like? A list of names in column A and overtime in ...

Trend Formula
What does the symbol E stand for in excel graphical trend formula? Is it 10, or exponential e? When I try to test the formulae I get the correct x=0 point but others are wrong; either way. I am using an x raised to the power 5 polynomial A number such as 1.234E-3 is to be read as 1.234�10^(-3) or 0.001234 Copying the trendline equation values can give terrible results unless you first format the equation for maximum precision Better yet use LINEST to but values into cells that you can reference to generate the forecast values See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm ...

Spreadsheet help!!!
I have a workbook with 2 worksheets. Information and Table in the worksheet Information, cell S24 if the number is greater than or equal to $100,000 but less than $149,999 then I'd like it to put in cell S25 the number $3000. Also if the number is between $150,000 to $199,999 i'd like it to put in $3500 in the S25 cell. Any help would be highly appreciated... Thanks alex -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4918 View this thread: http://www.excel...

Unable to add a new contact in CRM 4.0
Hi all Unable to add a new contact in CRM 4.0 as it errors out saying "Object reference not set to an instance of an object". Here is the event log information. Web Service Plug-in failed in OrganizationId: 01f0210d-6bf6-4c27-8919-00eccab73e2d; SdkMessageProcessingStepId: ab0fa3e5-3ceb-dc11-8840-0003ffbb159c; EntityName: contact; Stage: 50; MessageName: Create; AssemblyName: AlertCRM40Plugin.CRM40PostPlugIn, AlertCRM40Plugin, Version=1.0.0.0, Culture=en-US, PublicKeyToken=4406fa73b5445f20; ClassName: AlertCRM40Plugin.CRM40PostPlugIn; Exception: Unhandled Exception: System....

need help making chart
I have a data table which im trying to produce a some charts from but cant seem to get it right. bit hard to explain without seeing the table so I have provided the dummy workbook im working on at www.darkcity.nildram.co.uk/test.xls in this example ive got the big data table, and the type of chart im trying to produce form it us just below. the problem is ive had to manually create another smaller table under the big one in order to get the chart to look anything like what its meant to. i really need to have 3 charts produced based on whats in the big table if possible, I need charts by we...

Please help!!!!
I would like to know how to plot multiple sets of data on a scatter graph for example i have 3 sets of data for three types of lighting systems, red, green and blue i want to plot the voltage againt the current, and i want all the data on one graph so i can compare them, how do i do it? Frances I suggest you try the following. Plot a scatter graph as normal with your first set of data. Then to add more data to the scatter graph... Right click on chart and select <Source Data> and select the <Series> tab. Select <Add> in the series dialog box and type in the relevan...

HELP! how to turn off synchronization log messages?!
I have outlook XP on win2k, and my deleted items folder is being filled by "synchronization log" messages at the rate of 1 per minute. Where is a setting in outlook where I can disable/change that? Thanks! Rather than trying to turn off the symptom, perhaps you should look at the logs to see what the problem is? Aaron wrote: > I have outlook XP on win2k, and my deleted items folder is being > filled by "synchronization log" messages at the rate of 1 per minute. > > Where is a setting in outlook where I can disable/change that? > > Thanks! There is...

Changing a Number in a Column Using Arrays?
I am trying to change a number in the left column to the number in the adjacent column. My setup looks like this. Number to Change Change to this Number 182001 182000 182002 182000 193002 193000 Code New Code (from the Change to this Number column) 181000 181000 182000 182000 182001 182000 182002 18...

Index using a defined name array
I have defined 3 arrays using DefineNames: Boys_11_12 Boys_13_14 Boys_15_16 I then have defined a Combo dropdown with the following choices: Boys_11_12 Boys_13_14 Boys_15_16 When the user selects the Boys age group using the Combo dropdown, I then use the selection in an Index function: Index(Boys_(one of the three),...) I get an error when using the Combo dropdown, but it works fine when I type in the array name directly into the Index function. Its as if it knows the array when typed in, but not using the Combo dropdown. I created the Combo dropdown by typing in the arr...

link to another file or alternate means of providing help/instruct
I created a Word 2007 form and would like to somehow include a file containing instructions on how to use the form. Is there a way to link to or call an ancillary file from a Word template/form? I would like the user to be able to press a radio button to open and read the instructions, and then dismiss that file without closing or modifying the template/form.... Thanks, Ferg -- Nowisthetime4allgoodmen2come2theaidoftheircountry The difficulty with this is that you cannot force remote users to run the macros required and without them it is academic. There are two no-macro...

Help
CRM 3.0 refuses to install on my SBS 2003 R2 premium server. I was encountering the known issues with CRM 3 on SBS R2 using the CDs that came in the Action Pack subscription so I downloaded the updated CRM CDs from http://www.microsoft.com/downloads/details.aspx?FamilyID=7d418781-69ad-422d-92fa-87fdb2538e2c&DisplayLang=en This copy gave me a different set of problems. The default setup mode pops up a couple errors. The first one is the lack of full text search so I installed that and ran the setup again. The next error was: "The edition 'Workgroup Edition' of the specified SQL...

Chart Tiltes and axis are invisible, however they and print, help
When I make a chart, I can't see the Chart Title or Axis Labels. However, in print preview, and when printed, the Title and Axis labels Appear and print properly... How do of fix this so that I can see the Title and axis while working on the chart. ...

Upgraded both of my macs to Office 2004 and wish I could have Office x back! Any help?!?!
Hi, I am running a G5 Imac 1.8Ghz and a G4 Powerbook 1.5GHz and have been happily running office X on both machines for as long as I've owned them (1 and 2 years respectively) Before the Pbook, I also had an I book running Office X for 18 months with no troubles. I just installed Office 2004 2 weeks ago, and am consistently noticing, on both machines, serious system slowdown over the course of the day. To the point where, by mid-afternooon, my system needs to be restarted. The symptoms are identical on both macs (naturally the Pbook runs even slower than the G5 as these problems advan...

formulas in excel #2
I'm having trouble dislaying the results of my formulas. I've typed the formulas in and that's what is displaying instead of the results. I know I'm overlooking something but I just can't seem to figure it out. Can someone please point me in the right direction. Thanks in advance. Tom Hi Tom your cell is porbably formated as 'Text'. Goto 'Format - cells' and change the format to 'General'. After that reenter your formula HTH Frank Tom wrote: > I'm having trouble dislaying the results of my formulas. I've typed > the formulas in...

Loan
Hi, I have the following criteria: Outstanding principal: 616, 000/= Interest Rate: 13.25% Remaining tenure: 22 months Monthly Payment: 31, 672/= Lets say I increase my monthly payment by 2,000/= then what would the benefit (savings in interest and decrease in payment cycle). I know there are some web based tools that will allow me to calculate this. I'm looking for a formula to perform the above calculation. Any help would be appreciated. Thanks! On Dec 15, 10:55 pm, D2 <dhap...@yahoo.com> wrote: > I have the following criteria: > Outstanding principal: 616, 000 > Inte...

Check Book Formula
I'm a real green horn when it comes to excel, I've searched the data base and am pretty baffled by some of the questions...Wow....anyways, I feel kind of silly asking as this is probably very simple. I just don't know how i would go about setting up a check book style spreadsheet. The last three colums would be Income, Expense, Balance. I understand how to add or subtract, what I don't know is how to I tell the spreadsheet to SUBTRACT the expense to get the Balance or and ADD the income to get the balance in each row. Do I have to type a formula in each row to do ...

Hide formulas but not lock cells or contents
I've created a worksheet which changes depending on the entry in the Data Validation list but I like to hide all the formulas in this worksheet. I've tried selecting hidden in the Format/Protection and then selecting lock cells Protect worksheet in Tools/Protection but then this does not allow to select the drop down menu in the Data Validation box. I dont want to lock cells as they change with dynamically with data validation input PLUS I use Auto Filters but how can I still hide the formulas. Any suggestions Thx You COULD use Application.DisplayFormulaBar = False but the user c...

How Does One Limit Outlook Help To Outlook?
If I need to look something up related to Printer I don't want the help program to tell me how to "Find and install printer drivers for Windows Vista" followed by "Find printer drivers for Windows XP". I entered Printer into Outlook help and the first ten results were: Add or remove a printer Change your default printer Find and install printer drivers for Windows Vista Find a printer manufacturer's Web site Find printer drivers for Windows XP Get the version number for your Office program and information about your computer Change an ink or ton...

Error 3045 on an MDE file
Hi I hope you can help me with this it is driving me nuts. I've created a database which I needed to give multiple people access to but didn't want them messing so I created an MDE file. I ensured that the MDB file was set to shared, not compacting and set to run users not owners permissions before I converted. However, if one of the users goes into the database, it does not create and LDB file and if anyone else tries to get it, the error message: Could not use <name>; file already in use. (Error 3045) appears. Some of the tables in the MDE files were linking to and MDB fi...

test for a formula
Will someone help me write a macro to check if a selected cell contains a formula. Please Hi Lashio, Try something like: '=================>> Public Sub FormulaTest() If ActiveCell.HasFormula Then 'Do something, e.g.: MsgBox "Cell " & ActiveCell.Address(0, 0) _ & " is a formula cell" Else 'do something else, e.g.: MsgBox "Cell " & ActiveCell.Address(0, 0) _ & " is not a formula cell" ...

Outlook 2000 error, help
I'm not real familiar with outlook, we use outlook express in our office however new person wants to use "outlook" for email because he wants to transfer the contacts he had from his old company. We have office 2000 here and he can use outlook, that's fine. The problem is, I've imported his contacts and now when I open the Outlook program I get the following error: "A program is trying to access email addressess you have stored in outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose no. Allow access for 1, 2, 5...

Date Function Help
I need some assistance writing a formula that will count how many dates (in a list of dates in m/d/y format) are in each month. So... Column A [1/1/08, 2/7/08, 10/19/08, ...] Column B [Months, Jan, Feb, Mar, ...] Column C [Counts, {count of dates in January}, {count of dates in February}, ...] Make sense? The obvious answer would be to use another column to extract the month values and then just perform a COUNTIF function. But I can't use an extra column. Any help is appricated. Thanks, Shelton Try this in C1: =3DSUMPRODUCT(--(MONTH(A$1:A$100)=3DROW(A1)),--((A$1:A$100)<>"...

OT : Reading Help 2
Does anyone know of a free HTML Help 2 reader? Microsoft has completely redone their docs, so that for most help topics it's all or nothing: Install .Net and the entire Windows SDK -- which comes with Help 2 files and the necessary reader software -- or get no docs at all. That's about 1GB of stuff that I neither want nor. (Since most of it is just .Net docs.) I just want access to the latest versions of things like WMI help that used to come as independent downloads containing a CHM file. On Thu, 25 Mar 2010 15:25:00 GMT, mayayana wrote: > Does anyone kno...

HELP: retreiving forgotten passwords from Outlook
I've got Outlook 2003 and got a POP3 mail account set up. However I want to do a fresh installation of Windows and will lose my account details. I do not remember my password and was wondering how can I retreive my password for the "Logon Information" and also "More Settings | Outgoing Server" passwords. Since the passwords are in ****** format. I assume the passwords are stored in Outlook files encrypted right? KevinGPO, you wrote on Thu, 15 Dec 2005 09:20:48 -0000: > I've got Outlook 2003 and got a POP3 mail account set up. However I want to > d...

need help calculating intermediary values
I'm not well enough versed in spreadsheet use to calculate some intermediate values I need. I would be greatful for anyone who can offer a formula or suggestion. I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are beside it. The values in A & B change as they go from 1-30, they change at different rates. I have some intermediary values at given points but need to calculate what the values in between would be, based on the fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as best I can without including an attachment. Thanks. chris@cjalexander...