Formula Question... LookUP

I am using the follwing formula to calculate the last entry in a range..

=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05 Details'!C416:C428)

Is there a simple way to calculate the last but one ???

Any help greatly appreciated..

Thanks

0
G1 (145)
3/4/2005 4:15:03 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
341 Views

Similar Articles

[PageSpeed] 3

G,
rephrase your question. I'm not sure what you mean by this. Are you trying
to calculate its position on the worksheet?

O

-- 
Message posted via http://www.officekb.com
0
forum (466)
3/4/2005 5:48:25 AM
Hi!

>Is there a simple way to calculate the last but one ???

Does that mean if your formula returns the value in C420 
you now want a formula that returns the value in C419?

=INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
(A1:A15)),A1:A15),A1:A15,0)-1)

That will work as long as you don't have duplicate values 
in the range. Change the references to suit.

Biff

>-----Original Message-----
>I am using the follwing formula to calculate the last 
entry in a range..
>
>=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05 
Details'!C416:C428)
>
>Is there a simple way to calculate the last but one ???
>
>Any help greatly appreciated..
>
>Thanks
>
>.
>
0
biffinpitt (3172)
3/4/2005 6:18:30 AM
Hi Biff,

Thanks for the reply ..

You are correct in your assumption howvere there will be duplicate antries 
in this column ..

Thanks


"Biff" wrote:

> Hi!
> 
> >Is there a simple way to calculate the last but one ???
> 
> Does that mean if your formula returns the value in C420 
> you now want a formula that returns the value in C419?
> 
> =INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
> (A1:A15)),A1:A15),A1:A15,0)-1)
> 
> That will work as long as you don't have duplicate values 
> in the range. Change the references to suit.
> 
> Biff
> 
> >-----Original Message-----
> >I am using the follwing formula to calculate the last 
> entry in a range..
> >
> >=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05 
> Details'!C416:C428)
> >
> >Is there a simple way to calculate the last but one ???
> >
> >Any help greatly appreciated..
> >
> >Thanks
> >
> >.
> >
> 
0
G1 (145)
3/6/2005 11:59:03 PM
Try this if you might have blanks and duplicates within the range:

=INDEX(A1:A100,MAX(MATCH({"zzzzzzzzzz",9.9999999E+307},A1:A100)-1))
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"G" <G@discussions.microsoft.com> wrote in message
news:22415BDE-B43B-48C0-A2F7-536FCE7A4C10@microsoft.com...
> Hi Biff,
>
> Thanks for the reply ..
>
> You are correct in your assumption howvere there will be duplicate antries
> in this column ..
>
> Thanks
>
>
> "Biff" wrote:
>
> > Hi!
> >
> > >Is there a simple way to calculate the last but one ???
> >
> > Does that mean if your formula returns the value in C420
> > you now want a formula that returns the value in C419?
> >
> > =INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
> > (A1:A15)),A1:A15),A1:A15,0)-1)
> >
> > That will work as long as you don't have duplicate values
> > in the range. Change the references to suit.
> >
> > Biff
> >
> > >-----Original Message-----
> > >I am using the follwing formula to calculate the last
> > entry in a range..
> > >
> > >=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05
> > Details'!C416:C428)
> > >
> > >Is there a simple way to calculate the last but one ???
> > >
> > >Any help greatly appreciated..
> > >
> > >Thanks
> > >
> > >.
> > >
> >

0
ragdyer1 (4060)
3/7/2005 1:38:02 AM
Reply:

Similar Artilces:

Urgent Formula help needed please
I have 4 columns starting at C5 toF5 and at the bottom of each of the columns, C12 to F12 ,are the summed totals of the numbers in the columns Below these totals i have 2 more rows for which i need formulas. 1 row needs to look at the totals and whichever column has the lowest number return the column text label. So if it turned out that D12 had the lowest number, in D13 it would return the text label from D5 which in this case is "Home Study" and the row below would be looking for the maximum and return that columns text label I'm sure this is easy for most of you, but as...

Lookup/if statement?
I have 2 spreadsheets with data from 2 systems. What I need is to take specific data from sheet 1 and match it to the corresponding line in sheet 2. The problem comes in that each sheet only has a unique entry if I search based on a combination of 4 columns (item #, date, location and quantity). For any one of these search items there will multiple entries, but for the combination of all 4 there should be one unique entry. How can I accomplish this to compare the two spreadsheets? Excel has a series of Database functions, but to be honest they are hard for me to use. I have done ...

Old Question
On Novembre 04th 2009, I posted one Question labeld "Error 2001, Access 2007, Subform Bookmark", that had some replays from Jeanette Cunningham. Today I looked for them to review again, but they are not anymore available. I googled them I found that other sites still have them, but you don´t. What I have to do to continue with my request. Regards. On Wed, 10 Mar 2010 15:05:01 -0800, Jose Antonio Vial <JoseAntonioVial@discussions.microsoft.com> wrote: >On Novembre 04th 2009, I posted one Question labeld "Error 2001, Access 2007, >Subform B...

Help
I am a newbie to RMS I am trying to get my scanner to read a pre-weighed bar-code created from a stand-alone digi scale. The format is as follows: 0 203327 700695 (03327 = plu & 069 = price 69 cents) My scanners will read other barcodes in different formats (soda, dogfood etc.) and populates the item into the current transaction. The scanner also reads the above code (without the zero) 203327700795 but doesn't populayte the item into the transaction. Any and all help is greatly appreciated as we are slated to open tomorrow! Did you follow these exact steps? Rob Issue How...

How to key in a formula
I have this formula, b = n (∑ty) – (∑t) (∑y)/ n (∑t2) - (∑t)2 and for each column I already assign with the function. I just don't know how to key in in one column in order the calculation will come out automatically. Please help me!. Thanks in advance. Let's assume the t-values are in A1:A100, and the y values in B1:B100 The formula you need is =COUNT(A1:A100)*(SUMPRODUCT(A1:A100,B1:B100)-SUM(A1:A100)*SUM(B1:B100))/(COUNT(A1:A100)*SUMSQ(A1:A100)-SUM(A1:A100)^2)) BUT!!!! I think you are computing the slope of a line so why not use =SLOPE(B1:B...

Queries to lookup a value
I have a situation where what I want to do is: Combo Box one [Field 1] shows all the options e.g.: (Fruit, Meat, Breaed) Combo Box two [Field 2] shows all the options that have been limited by [Field One] e.g.: (Apples, Pears, Oranges) Combo Box three [Field 3] shows all the options that remain after filtering by Field1 and Field2 Once I have that I want to have a form field that Looks up [Field4] and puts it in that form field. This is to be able to say give me fruit, apples, fuji, and then put in the unit price field the correct cost. I could really use help on this!!! Thanks Searc...

Formula for invoice aging
If a number in a cell is less than, but greater than (30- 60 days past due), return this cell, if not "zero". ie =IF (G3>30,H3,0):IF(G3<61,H3,0), this formula enters "#value" in the cell it is not true in. How do I fix it to enter "0" in those cells it is not true? Try it this way: =IF(AND(G3>30,G3<61),H3,"") HTH Chris "Michelle" <msoldano@forensic-engrs.com> wrote in message news:057701c38d1e$af4b7880$a401280a@phx.gbl... > If a number in a cell is less than, but greater than (30- > 60 days past due), return this c...

Exchange protocol question..
What protocol (or port number)does my outlook uses, internally, to connect to exchange 2003? To make the question clear, I am not using pop or smpt or IMAP? The reason for my question is that my boss has treo 650 smart phone with Internet connection. Currently, the way I set here up is to use VPN and connects through versa mail to exchange through the exchange internal IP address. If I can map the Internal ip address, of exchange, with an external ip address through port something then I would not nee VPN any more. Is that possible? Sorry for any confusion. On Thu, 23 Feb 200...

formula help: returning a range that is the 1st row of an existing range
Hi I have a range defined over a table of data, eg myTestRange = B1:T100 The 1st row of this range is the table field/column headings. I want to find the index number of a particular field. Eg if I had the following table: Name, Address, phonenumber, Age, Sex data..... my table range would be defined over the table and I want to know the col index of say the "AGE" field - which would be 4 in this case as its the 4th column in this range. I know the MATCH command returns the col index that I want, but what I dont know how to do is tell Excel to look over the 1st row of my range. ...

Coverting All incoming email question...
We are using CRM 1.2. When I am logged in as a user and I go to tools, options, and then select the activities tab, there is an option to "convert in-coming email". There are two options. By default, we have the "only e-mail about existing Microsoft CRM records" selected. My question is that if we select the other option "all incoming e-mail", and we change the setting on the server to allow this, can the user toggle back to the other selection. I just want to make sure that it can be toggled by the user. I am afraid that if I allow this I will alway...

XSLT question #2
Hi Can XSLT be applied for transforming .txt files or are they applicable only for xml to xml transformation. Currently all the clients send file to a server in one particular format. The format of the file which the server accepts is going to change. So we need to build an intermediate system which will do the transformation from the old format to the new format. Can I use XSLT for doing this? Is XSLT applicable only when xml to xml transformation is involved? Regards rjn *** Sent via Developersdex http://www.developersdex.com *** RJN wrote: > Can XSLT be applied for transfor...

Calendar Question
Pls, how do I add, what formula do I enter in Row 1, Col B that will add " 'n' buiness" days to a date in Row 1, Col A? Thank You, Wayne Try the NETWORKDAYS function. See help for usage. BTW "Row 1, Col B" is more easily expressed as B1 Gord Dibben MS Excel MVP On Sun, 22 May 2011 16:04:14 -0400, wgd.roaming@verizon.net wrote: > >Pls, how do I add, what formula do I enter in Row 1, Col B that will >add " 'n' buiness" days to a date in Row 1, Col A? > >Thank You, Wayne > > ...

Userform Question #2
Am I able to keep a macro running even if a userform is showing, I have put a code in the this workbook module userform1.show call start Thanks in advance Greg you can put a macro on the userform on activate event Private Sub UserForm_Activate() call start End Sub double click on the form to get access to the code window Eric >-----Original Message----- >Am I able to keep a macro running even if a userform is showing, > >I have put a code in the this workbook module > >userform1.show > >call start > > >Thanks in advance > >Greg > > &...

Multi-threading question regarding MFC in a DLL..
Pardon for the stupid noobish question: WinXP Pro, VS .Net 2003 I have a client application which uses MFC (MFC dynamically linked) I have a DLL which uses MFC (regular DLL, MFC dynamically linked) The app calls ::LoadLibrary to load in the DLL, the DLL (when it initializes) creates a thread for performing calculations. The app then uses ::GetProcAddress to explicitly link to the exported DLL functions. Each exported DLL function's first line of code is: AFX_MANAGE_STATE( AfxGetStaticModuleState() ); As a test, I do something I know is bad, I have a string that is accessed (for r...

rookie question
Hi! Is there any free program/plug in which adds some new graphs to my Excel 2003? Or some not free programs? Thnx! There are lots of Excel-compatible programs which offer more charting features. These include: Sigmaplot at http://www.systat.com/products/SigmaPlot/ Origin at: http://www.originlab.com/ Crystal xcelsius at: http://www.xcelsius.com/ Statographics: http://www.statgraphics.com/ best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Nevermind" <nevermind@hotmail.com> wrote in message news:f4jfql$708$1@ss408.t-com.hr... > H...

Another PRF question...
Hi again, In the PRF Whitepaper it discusses translating registry entries into PRF entries. Page 11 shows a table referencing registry data entries to MAPI Data Types. The problem I have is that the value I want to change has a registry value (0x0102) that is not listed in the table. Is there an extended table available detailing more values? Cheers, Mark ...

Formula for unique records
I am trying to make a report a little more user friendly for a novice excel user, and I am trying to use all formula's instead of pivotables on large data sources. Is there a formula that will pull unique records out of a list of customer #'s as an example without having to use auto filter which would then require more manual intervention. I am then using sumproducts using the customer number to create the report. >I am trying to use all formula's instead of pivotables >on large data sources. How large is large? More than a few hundred rows? Formulas to e...

How do I count the number of brackets within a formula (cell)?
I am reviewing vehicle usage. An example of a formula under review is as follows: =(5-2)+(6-2)+(8-3). This will add up the kilometers. The 2nd column (the problem), I want to calculate is the number of trips. Each bracket is considered one trip. In the above example that would be a total of 3 trips and total of 12 km. One way, using a UDF: Public Function NumTrips(rng As Excel.Range) As Variant With rng If Not .HasFormula Then NumTrips = 0 ' or CVErr(xlErrRef) Else NumTrips = Len(.Formula) - _ ...

LOOKUP SEVERAL VALUES
Hi All I need to populate a cell in spreadsheet 2 based on the values of several cells in spreadsheet 1 and not sure if VLOOKUP can help here. On spreadsheet 1 I have bb in B2, cc in C2, dd in D2 ,ee in E2 and OO in O2 if all of these values appear on spreadsheet 2 then I want to populate Q2 in spreadsheet 2 with same value qq as in spreadsheet 1. I have an array to check so it would be applied for range B2:Q200. Please could someone advise which lookup function I can use. Thanks Try this in Q2 of sheet 2: =IF(AND(Sheet1!B2=B2,Sheet1!C2=C2,Sheet1!D2=D2,Sheet1!E2=E2,Sheet1!O2=O2),Sheet1!Q...

Form questions
Hi, I have 2 forms. Form A and Form B. They both have 2 fields called "Company" and "Registration Number". I know it's possible to automatically have the one of the fields filled up in Form B when details have been input in Form A. What I want to know whether it's possible to have more than 1 field being filled up automatically?. Below are the codes for Form A Private Sub Person_Details_Click() Dim strOpenArgs As String Dim strCriteria As String Dim strOpenArgss As String Dim strCriterias As String Dim strFormName As String On Error GoTo Proc_Error strFormName...

Date Formulas #3
How can I make a cell correct the year of a date Hi Charlene not really sure what you're after here if you have 1/1/05 and you want to display just 2005 then right mouse click on the cell and choose format cells / on the numbers tab, choose custom and then type yyyy in the white line and and click OK. however, this just displays the date showing only the year, if you want to extract the year only to another cell you can use =year(A1) where A1 contains the date if you're after something else, please type a few examples of the data you have and what you want to see. Cheers JulieD...

insert formula using VB
Hi, I have some code which creates a formula within a range of cells, calling an add-in function (Bloomberg market data). the code is as follows: If strRegion = "US" Then For lngRow = 2 To maxRow If Cells(lngRow, 1).Value <> "" Then strCellAddress = Cells(lngRow, 1).Address Cells(lngRow, 1).Value = Cells(lngRow, 1).Value & " US" strFormula = "=blp(" & strCellAddress & Chr(38) & Chr(34) & " Equity" & Chr(34) & "," & Chr(34) & "NAME" & Chr(34...

excel formula #4
I have in column A1:A365 all dates of the year 2005 and in colum B1:B365 the days of that date. In column D1:D365, E1:D365 and F1;F365 I fill in a X for every actio Succeeded. SO if it is 1 January 2005 and the action in D1 and E1 and F1 are al succeeded I fill in those fields an: X I have in a cell a formula what checks how many % (procent) succeede of the fields who are filled. But when it is December he also calculates the X that are filled in A etc. Now I would like to make a formula which calculates how many action went good in the last 30 days is this possible? So that he only chec...

Time Formula #7
I was given a file that looks like this: Day of Wk Time Number of Minutes Tuesday 1148A 3 Tuesday 1151A 1 Wednesday 0301P 1 Sunday 1202P 5 Saturday 0530P 2 Monday 0542P 10 Trying to determin how many minutes were spent on the phone during non-work days/times (M-F, 8-5) 1. How do I convert the time into a useable format? 2. Please help w/formula =TIME(LEFT(A6,2),MID(A6,3,2),0)+(RIGHT(A6)="P")*0.5 and format as time. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Cindy Smith" <CindySmith@discussions....

template or formula for start time -finish time -total hours ple
please could I have a formula or function to enter start time-end time and show total hours/mins for excel =B1-A1 =B1-A1+(A1>B1) =MOD(B1-A1,1) with start time in A1 and end in B1 using excel time format hh:mm:ss the first 2 formulas work if start is before and end is after midnight -- Regards, Peo Sjoblom "cc" <cc@discussions.microsoft.com> wrote in message news:CD5F3A3B-7101-441C-A18A-93DE69C2D31C@microsoft.com... > please could I have a formula or function to enter start time-end time and > show total hours/mins for excel ...