#### Trouble with offset formula

```Hello,

I am trying to return a unique value into a cell from a large tabl
using two offset criteria as follows:

=OFFSET(I3,COUNT(A:A)-1-E3,8)

The formula is working in cell I3, I wish for cell I3 to return th
value that is COUNT(A:A)-1-E3 rows down and 8 columns across from it.
The column offset is easy, it's always 8 columns away.  However, th
row offset formula calculates correctly only for the first row o
observations, but when I copy it down my worksheet, it does no
calculate correctly for the second, third, and so on (which shoul
change because E4 has a different value than E3 and E5 has a differen
value than E4, and so on).

I was pretty sure this formula should work, but it doesn't.  Can anyon
tell me why it's not calculating the correct row offset for higher ro

Any help would be much appreciated!

Thanks,

-Pet

--
Message posted from http://www.ExcelForum.com

```
 0
7/16/2004 6:52:06 PM
excel.misc 78881 articles. 5 followers.

6 Replies
353 Views

Similar Articles

[PageSpeed] 10

```Hi
maybe you mean
=OFFSET(\$I\$3,COUNT(A:A)-1-E3,8)

Otherwise you may give an example of your data and the expected results
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

> Hello,
>
> I am trying to return a unique value into a cell from a large table
> using two offset criteria as follows:
>
>
> =OFFSET(I3,COUNT(A:A)-1-E3,8)
>
> The formula is working in cell I3, I wish for cell I3 to return the
> value that is COUNT(A:A)-1-E3 rows down and 8 columns across from it.
> The column offset is easy, it's always 8 columns away.  However, the
> row offset formula calculates correctly only for the first row of
> observations, but when I copy it down my worksheet, it does not
> calculate correctly for the second, third, and so on (which should
> change because E4 has a different value than E3 and E5 has a
different
> value than E4, and so on).
>
> I was pretty sure this formula should work, but it doesn't.  Can
> anyone tell me why it's not calculating the correct row offset for
>
> Any help would be much appreciated!
>
> Thanks,
>
> -Pete
>
>
> ---
> Message posted from http://www.ExcelForum.com/

```
 0
frank.kabel (11126)
7/16/2004 7:27:25 PM
```Pete,

I'm not clear on this.  Should all the copied down formulas use E3 (not E4,
E5, etc.)?  If so, make that cell reference absolute, \$E\$3.  To do that, in
edit mode or in the formula bar, put the cursor on or next to the E3 cell
reference, then press then F4 key, then Enter.  Now copy down again.  I have
a feeling that this isn't it, though.

Give us an example of a failing formula.  Tell what's in E4 (or whatever
relevant cell), and
what it returns.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

> Hello,
>
> I am trying to return a unique value into a cell from a large table
> using two offset criteria as follows:
>
>
> =OFFSET(I3,COUNT(A:A)-1-E3,8)
>
> The formula is working in cell I3, I wish for cell I3 to return the
> value that is COUNT(A:A)-1-E3 rows down and 8 columns across from it.
> The column offset is easy, it's always 8 columns away.  However, the
> row offset formula calculates correctly only for the first row of
> observations, but when I copy it down my worksheet, it does not
> calculate correctly for the second, third, and so on (which should
> change because E4 has a different value than E3 and E5 has a different
> value than E4, and so on).
>
> I was pretty sure this formula should work, but it doesn't.  Can anyone
> tell me why it's not calculating the correct row offset for higher row
>
> Any help would be much appreciated!
>
> Thanks,
>
> -Pete
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
nowhere1083 (630)
7/16/2004 7:29:22 PM
```Thank you for your suggestions.  However, as you both advised, I though
it would be better to post my file here, instead.

I have a textbox in my worksheet which explains what I am trying t
do.

Thanks again for all your help!

-Pete

--
Message posted from http://www.ExcelForum.com

```
 0
7/16/2004 9:28:43 PM
```Hmmm....not sure if my file made it...

I could e-mail it to you, I suppose

--
Message posted from http://www.ExcelForum.com

```
 0
7/16/2004 9:33:12 PM
```Hi
don't post your file (as it is not recommended to attach files). Simply
copy your current formula and paste it as plain text

--
Regards
Frank Kabel
Frankfurt, Germany

> Thank you for your suggestions.  However, as you both advised, I
> thought it would be better to post my file here, instead.
>
> I have a textbox in my worksheet which explains what I am trying to
> do.
>
> Thanks again for all your help!
>
> -Peter
>
>
> ---
> Message posted from http://www.ExcelForum.com/

```
 0
frank.kabel (11126)
7/16/2004 9:34:43 PM
```....Or, here is an image of my worksheet (provided it uploads, o
course).

Thanks again,

-Pete

--
Message posted from http://www.ExcelForum.com

```
 0
7/16/2004 9:37:29 PM

Similar Artilces:

I posted this a couple of days ago but doesnt seem to have appeared so I hope this is not a duplicate. Original formula from previous post... =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*24 I have changed the formula to =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*C13 As a test I have put in aircon running for 1 hour between 10-11 am So this should return a value of .30 It is ...

This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C61A25.29EC1E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I didn't receive a response on my last post so I worked on my problem = some and came up with some results. Could someone look over my formulas = and tell me if I can achieve this same result any easier? Here is a sample of the worksheet: D E F G H I J K L M N O=20 22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total=20 23 off 12 11 11 11 11 off 40 15 1 10.00 \$645.00=20 ...

Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

Copy and Paste without copying Formulas
I have created a workbook with several sheets. The first sheet consists of a master list of 8000 numbers. The second sheet is where I paste a smaller list of numbers. The workbook performs a search and returns numbers that match on the third sheet. The third sheet also has formulas. I want to be able to copy and paste the resulting info on the third sheet to another workbook without copying any of the formulas......I just want the info. Any suggestions?? Ken Copy>Paste Special>Values>OK>Esc. Gord Dibben Excel MVP On Thu, 18 Nov 2004 14:21:13 -0800, "Ken"...

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

how do I insert a filename into a formula from another cell?
I have a spreadsheet with a filename in a cell. I would like to reference that filename in a formula. I can't seem to get it to work. "+cell number" doesn't work. Any suggestions? You would normally use INDIRECT to do this, along the lines of: =INDIRECT("["&A1&"]Sheet1!C2") where A1 contains your filename (with the .xls extension) and you are trying to return data from C2 on Sheet1 of that file. However, INDIRECT will only work with files that are open, so you would have to have the file open for this to work. Hope this helps. Pete On...

Array formula
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

Ability to create formula to calculate benefit or deduction code
Would like to use a benefit or deduction based on a formula to calculate the amount instead of choosing only a flat amount or % as it is currently set up in GP version 9. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Commun...

Results of formula vs real numbers
HI. How do you change a formula to read the value as a result of a formula and not necessarily as a real number? Example: In G6, i have the following formula that gives me the hour of a time in F6 =IF(F6>0,MOD(F6-"1:00",1),"") F6 contains 00:30:08, The result is 23 In another cell, I am using this formula, but it doesnt recognize the 23. =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23")) The 23 is actually the HOUR of time so it isn't a whole number or real number persay. It is a rounded time to the hour. Yes, but it isn't a text ...

is there a way i can protect the formulas without protecting and passwording and identifying various ranges? the user accidentally enters data into a calculated cell and wipes out the formula. thank you, mike g Mike, Set the cells that the user can change to unlocked using Format, Cells, Protection, set those they can't to locked. Protect the Sheet. Robin Hammond www.enhanceddatasystems.com "work" <mike@radiant.net> wrote in message news:42140299.10B3@radiant.net... > is there a way i can protect the formulas without protecting and > passwording and identifying ...

Ignoring #DIV/0! in a formula
Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried messing around with IF statements but I cant seem to figure it out =IF(ISERROR(cellref),erroraction,noerroraction) =IF(ISNUMBER(cellref),noerroraction,erroraction) You can also use it in range functions, such as =SUM(IF(ISNUMBER(range),range)) but you must array enter (Ctrl-Shift-Enter) Jerry DLZ217 wrote: > Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried > messing around with IF statements but I cant seem to figure it out "Jerry W. Lewis" <post_a_reply@no_e-ma...

If, say, =SUM(I2:I10) totals "0", how to change formula to put a "0" in I11?
I know this is going to be simple but everything I've not got anything to work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0" in I11 if there aren't any values anywhere in I2 to I10? Thanks. :oD If there are no values to sum the formula should already be returning 0. What result are you getting? Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:OV\$7dJLnHHA.3704@TK2MSFTNGP02.phx.gbl... >I know this is going to be simple but everything I've not got anything to >work. How can we change f...

Minus sign trouble
Periodically we need to download information from another source that ends up placing the minus sign at the END of the number. This naturally isn't readily recognized by Excel as a negative number. Can anyone advise me as to how to do this with relative ease?? Dave French Windows 2000 Office 2000 take a look here: http://www.mcgimpsey.com/excel/postfixnegatives.html In article <#y#k6iQjDHA.1940@TK2MSFTNGP09.phx.gbl>, "David French" <nospam@nospam.com> wrote: > Periodically we need to download information from another source that ends > up placing ...

Having trouble with copies (being treated as embedded objects)
I have a system with Windows XP Professional installed. I recently installed an old version of Microsoft Office 2000 Premium and have developed a problem. I noticed that sporadically and for long periods of time I could not copy cells in Excel as I would get strange error messages. I downloaded an open source spreadsheet and then realized the same problemwas sporadically happening to that program thus indicating a possible Windows XP problem. I have now discovered that simple copies of cells within the spreadsheets are being copied as embedded objects instead of the straight-forw...

Mass change of transaction types to Transfer trouble. Suggestions
background: I am currently using Money 05 and have transfers forms on. In 2002 I had money all up to date in my banking life and stopped using it because of one problem or another. I just logged onto each of my banks and downloded all the missing information to date. In one bank I have a checking and savings account in. The file I downloaded from the bank has transfers incorrectly marked as deposits and withdrawls and nothing linked together. I also have money 07 trial installed on a different pc running a copy of the above database (for testing.) What I want to do is select all ...

coping formula
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

MATCH formula #2
I have a spreadsheet that lists all the types of machines and thei model numbers. When a customer places an order for a specific machine I need to reference the other workbook in order to get the model number Is there a way to have this done automatically. For example: when enter a machine type (CE-10), I want the model number for that machin to be entered automatically in another cell, without having to open th other workbook and copy and paste the number myself. I considered dat validation (using a list) but can't reference another workbook. Any thoughts? Thanks, De -- da ------...

Formula Cell not updating
I have a workbook with several worksheets in it. The sheets are not related to each other except that I used the copy worksheet feature to add sheets. There is a formula cell which totals the two cells to the immediate left on each worksheet. For some reason, when I change one of the values in the cells being added, it doesn't update the total, unless I retype the formula in the cell. I have had this problem one other time in the past. I'm not sure how to fix it. -- Sincerely, Beverly76 Hi you may check if automatic calculation is enabled ('Tools - Options - Calculate...

Excel: Replace strings with a Matrix formula
Hello NG, the following sheet: - In column A are words in German. - In column B are the translated words in English. - In C1 is one English clause with only one single German word in it. ToDo: I want translate this word in English and write it o D1. But I dont want to use VBA or more then one cell for the calculation. Such solutions I can make for myself. I'm working since several years with Excel and VBA. Currently I'm playing with this matrix formula. (I have translated the Excel function names from German to English, but I dont know if I used the right words.) {=IF(ISERROR(SEARCH...

MTA Mounting Trouble
Having problems getting the Exchange 03 server backup and running. Have followed ALL of /disasterrecovery and looked all over for log file backups. I do not have any log files but do have priv1.edb and pub1.edb in \mbddata. All other AD issues have been recovered. Under App log I am having 9095&9096 followed by 9098. When I try to start the ExchangeIS I get error 7024. The MTA will not mount nor gives the option to mount, have run the eseutil on the DB's but still do not have log files. This is the first installation of any Exchange in a 2000 domain. Did a /removeorg and then ...

>< formulas
A spreadsheet containing two colums. Column A has dollar amount entries. Column B has date entries. These entries are entered throughout the month and every month of the year. I need a formula that will sum the amounts in column a only if they are during the month of march from column b (for example) "mpiton" <mpiton@discussions.microsoft.com> wrote in message news:F1965B8B-EF82-41A6-89CF-86FF72706DB2@microsoft.com... > A spreadsheet containing two colums. Column A has dollar amount entries. > Column B has date entries. These entries are entered throughout th...

Excel formula help #5
I've come up against a problem in Excel, I need help to figure out a formula. A B C D 1 Number Of Months 24 �60.00 �1,440.00 2 Number Of Weeks 96 �15.00 �1,440.00 I will try to make this clear to understand, what I need is when I change cell C2 either adding more money or taking away, I need cell B2 to reflect the change either increasing the amount of weeks needed to get to D2s total or decrease depending on what was changed in C2. Any advice or help would be much appreciated. On Oct 10,...

Lookup Formula
I have an Excel sheet that shows hourly rates based on the number of hours a customer purchases. Example Column A Column B Column C (Hours)From: (Hours)To: Hourly Rate 1 40 \$35 41 80 \$32 81 120 \$30 I have a seperate column under a different tab where a sales rep enters the total number of hours. I would like the formula to automatically lookup the chart above and show the appopriate hourly rate. Thanks, Donne Hi - best to ask the Excel gurus in an Excel n...

Protecting Excel Formulas
Can anyone help with the protection of part of an Excel worksheet (Excel 2003)whilst still allowing sorting and filtering of data? Individual users paste data into the worksheet and, despite comprehensive instructions, sometimes manage to delete / overwrite existing formulas. The individual users need to sort and filter the whole worksheet after their "input" so the basic procedure of locking and protecting the cells containing the formulas isn't an option. Any help would be most welcome. If users input data to w/sheets they do not need access to cells containing ...