Offset Function For Cash Flows In Different Years

Sep 5, 2007

I have got three period like this

Period: 2010 2015 2018
Cash: 100 200 300

I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...

The three periods described above will be changeable, so the cashflows will adjust accordingly...

View 9 Replies


ADVERTISEMENT

Monthly To Quarterly Cash Flows

May 13, 2014

I am building a Discounted Cash Flow.

I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.

View 1 Replies View Related

Payback & Discounted Formulas For Uneven Cash Flows

Nov 7, 2006

formula in excel to calculate payback and discounted payback periods with uneven cash flows.

EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444

View 8 Replies View Related

Using OFFSET Function To Return The Value From Another Cell With Row And Column Offset

May 31, 2014

I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.

For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.

I have attached a sample worksheet that provides some examples.

View 3 Replies View Related

Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function

Apr 13, 2007

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View 9 Replies View Related

Macro Using Transpose Function For Multiple Years Of Data

Jul 6, 2014

I need to create a macro in excel that will take data for a particular year in vertical rows and transpose it to horizontal rows.

What the data originally looks like:

380204198401014.72
380204198402016.34
380204198403015.66
380204198404013.71
380204198405013.86
380204198406013.51

[Code] ........

What I want the transposed data to look like:

StationYearJanFebMarAprMayJunJulAugSepOctNovDec
38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25
38020419853.194.211.021.292.372.416.082.500.143.196.122.04

I have never written a macro before and have tons of records (over 40,000) so using this will save me a lot of time.

View 5 Replies View Related

Select Specific Month Of Many Years Of Data With Any Function?

Aug 14, 2012

Is there any way to select specify month of the many years of data with any function?

View 7 Replies View Related

Adding Years To A Date (taking Into Account Leap Years)

Aug 28, 2007

I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.

View 9 Replies View Related

Vlookup Function Together With An Offset Function

Oct 26, 2008

I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.

The situation is as follows:

I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.

I have though of using the offset function, but i cannot figure out how to make this work.

View 8 Replies View Related

Using OFFSET Function

Dec 1, 2009

In row E10:Z10 I have a series of values and in row E6:Z10 I have dates associated with each of the values in row 10.

I would like to create a formula to look in E10:Z10 for the maximum value and then return the date associated with that value. For example, if the maximum value is in P10, I would like the formula to return the date in P6.

I have tried without success to use the OFFSET function as follows:

=OFFSET(MAX(E10:Z10),-4,0)

View 3 Replies View Related

Using The OFFSET Function In VBA

Jan 16, 2009

I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:

View 5 Replies View Related

Offset Function

Feb 3, 2010

I would like to use the offset function as follows

Each Month a new column will be Inserted in Column V

I need to sum data starting three columns to the right of column V and then two column to the right of that plus BC15 (this will also change each time a column is inserted in Col V

I have used a sum formula before inserting a new column showing you what i am trying to achieve ...

View 9 Replies View Related

Match Or Offset Function

Jul 10, 2008

I'm trying to create a formula that would return the value of the column next to it. Looking at the below example, in a new cell I would want to be able to return the value 7 based on the fact that it is in category b.

I would think an if/offset/match function would do this but I can't seem to figure it out as it may require nesting.

categoryvalue
a1
a2
a3
a4
a5
a6
b7

View 9 Replies View Related

Implementing Sum Function And Row Offset

Apr 10, 2012

I have a total line that gets moved down with each additional newly inserted row from a vba macro. I need the total line to sum all rows in column "B" beginning with the row immediately above the total line and continuing to 30 lines above that.

View 2 Replies View Related

Sumif With Offset Function?

Jul 2, 2014

I was wondering if there was a way to use sumif and offset to guide the summing column in the example below. Perhaps I need to add an additional column, then formulate a solution. I want to sum the daily returns for the TSX when the slope is positive (green), and sum the S&P when it is negative. Currently, on signal day, I am summing that day's return, which I don't want to do. I want to sum starting on the next day (blue). The B column switches back and forth from positive to negative, so I will summing different data periods going back 1000 days. I am summing the TSX returns and S&P returns seperately to make it easier. Currently I have in C1=(sumif(b2:b1000,">0",c2:c1000)) Same for D2.

Date
Slope
TSX
S&P

sumif
sumif

06/30/14
0.002774
0.34%
0.19%

[code]....

View 3 Replies View Related

Offset Function With Validation

Jul 5, 2007

find attached the excel sheet. In sheet1 I have named the range I have inserted a Name(Test) for range $A$1:$A$2(range contains only texts) and have a list validation on cell $B$1 using the name. This works perfectly. In sheet2 I have named the range I have inserted a Name(Test1) for range $A$1:$A$2(range contains only numerics) and have a list validation on cell $B$1 using the name. This doesn't work.

View 3 Replies View Related

Offset Function With Criteria

May 22, 2008

I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?

View 4 Replies View Related

Replace The Offset With Find Function?

Nov 25, 2012

I just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.

View 5 Replies View Related

Excel Chart Using OFFSET Function

Mar 14, 2012

I have an Excel Workbook with data in Sheet1 and a chart in Sheet2.

I use the OFFSET function to define the data range. In Sheet2, I have 3 cells, B2(column), B4(row) and B6(period). I have slider to adjust the values in B2 and B4.

I define the data range at follow.

Xchart =OFFSET(Sheet1!A1,Sheet2!B4,0,Sheet2!B6,1)
Ychart =OFFSET(Sheet1!A1,Sheet2!B4,Sheet2!B2,Sheet2!B6,1)

When I tired to put in this into value series

=Book1.xlsx!Ychart

The error message ...

Reference not valid. Reference must be to an open Worksheet.

View 3 Replies View Related

Lookup List Using Offset Function

Apr 17, 2012

I am looking to do lookup list using the offset function. Scenario: I have 5 dealer groups, each with there own dealers. Each dealer has a list of customers. Once I select the customer it will bring up a list of their vehicles. I am stuck on selecting the customers and vehicles once the dealer group and dealer are selected.

View 1 Replies View Related

Offset Formula - Logical Function Using OR

Jul 10, 2013

I have following formula that returns #value!

=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),
COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))

Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.

View 2 Replies View Related

Using A Formula As The Reference In An Offset Function

Sep 2, 2008

Is there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?

For example, rather than this:
=OFFSET(H3,0,-7,1,1)
I want to do this:
=OFFSET(max(H:H),0,-7,1,1)

Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.

View 9 Replies View Related

OFFSET Function With Pivot Table

Jun 22, 2009

I have a pivot table that has this structure:


product-id fecha entrega SUM of quantity
Q-NATA90 22-jun 15 13-jul 15 03-ago 15
Q_nata is product code (fields)
22-june, etc are the dates of the individual orders (fields)
15 is the amount of each order (data)

I tried to set an offset function in a cell outside the table that, refers to the first DATA cell (upper 15) and returns the date of the order, like this:

=DESREF(IMPORTARDATOSDINAMICOS("cantidadoriginal";$A$3;"producto_id";"G-CLA2775M";"fechaentrega";185);0;-1;1;1)

*DESREF is offset in spanish, importardatosdinamicos is IMPORTDYNAMICDATA or something like that.

But excel tells me thereīs an error in the formula and it doesnīt let me use it...I canīt find the error.

View 9 Replies View Related

Dynamic Range - OFFSET Function??

Jun 24, 2006

I want to use a dynamic range in order to use this formula below in column A as long as there is something entered in column H = SUMIF($N:$N,A5,$I:$I). any ideas on syntax and will this be using the offset function within excel. I know I have posted a couple times about getting this formula to replicate itself down certain columns as long as someone enters text in another. I looked into the offset reply information somone posted but it seemed a little over my head.

View 4 Replies View Related

Offset Function Linking To Another Workbook

Jun 20, 2007

ive managed to come up with this formula: = OFFSET( 'G:gailMVTR2007[B052,B261A.xls]B261A45'!$B$5,COLUMN()-3,0,1,1)

which pretty much does the same job as a paste special/translate, this works fine when both files are open and updates all the values that i could ever enter, however, when i open only the file containing the link and press "update" it returns a "#value!" error, i dont understand because it works fine when the other sheet is opened and there are no values other than a single number used.

View 6 Replies View Related

Offset IF Function That Checks For Mathematical Errors?

Aug 11, 2014

I have an IF Function that I want to use to check mathematical errors between two sheets. For example Sheet2 is where I put all my material prices and Sheet1 cell A1 shows the total amount that is one Sheet2. On Sheet1, I have an IF Function in B1 that does the same calculation A1 does, but will display "ERROR" if A1 does not Match B1.

The IF Function works, my problem is that I want this to work from B1:B100 on Sheet1. But on Sheet2 the next SUM would be from row 42 to row 59.

How can I make and IF Function from B1:B100 on Sheet1 offset every 20 rows on Sheet2

For example:

Sheet1 B1 would be:
Formula:

[Code].....

Sheet1 B2 would be:
Formula:

[Code] ......

I need to do this without having to manually enter each range to sum up.

View 7 Replies View Related

Offset Function Not Capturing Entire Range

Feb 9, 2014

My worksheet has 1096 rows of data including headers. I am trying to create dynamic named ranges using the OFFSET function but when I check to see if they are capturing the entire range I see they are not.

Here is my formula:

OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA),1)
OR
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA)-1,1)
OR
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA)-1)
OR
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA))

I have tried all four but every time it continues to stop on row 409. I have tried to name different columns and they all stop short too.

View 10 Replies View Related

Data Validation List - OFFSET Function

Apr 20, 2012

I have the following formula in a Data Validation List:

=OFFSET(B8,MATCH(J2,
(CHOOSE(B7,D1Array,D2Array,D3array,D4Array,D5Array,D6Array)),0),1,COUNTIF(CHOOSE(B7,D1Array,D2Array, D3array,D4Array,D5Array,D6Array),J2),1)

In B8 it is the formula =VLOOKUP(B7,LookupTable,2,0). and the lookup array is this 1$H$112$J$113$L$114$N$115$P$116$R$11

So the value returned in B8 will be a the start reference for the OFFSET function. This works but it retuns blank values and not the values in the arrays.

View 3 Replies View Related

Offset Function - Get Text Form Cell

Nov 22, 2013

I have this offset function working:

=OFFSET(calculos.xlsx!_5260;10;5)

The cell name "5260", is also entered as text in cell A1, in the current workbook (not calculos.xlsx). How do I refer excel to get the text from cell A1, instead of having to enter it manually.

View 3 Replies View Related

Dynamic Print Range - Offset Function

Jul 19, 2014

I have a worksheet where the number of rows will change on a weekly basis and I would like to print the sheet out without having to change the print range every week.

I have tried to use the OFFSET function in the print range in print setup but once this has been entered and printed, it changes to the actual print range the OFFSET returns.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved