I want to use the address function to indicate the range in the calculation of a slope:

cell A2 contains the value 20

=ADDRESS(A2,2) returns $B$20

=ADDRESS(A2,3) returns $C$20

Cell B20 and C20 are the starting cells for calculating the slope:

=slope(B20:B25,C20:C25)

But putting the address function in the slope calculation, like

=slope(ADDRESS(A2,2):B25,ADDRESS(A2,3):C25) does not work!

Why can't I combine these functions?

Is there another (simple) way?

Mayo

cell A2 contains the value 20

=ADDRESS(A2,2) returns $B$20

=ADDRESS(A2,3) returns $C$20

Cell B20 and C20 are the starting cells for calculating the slope:

=slope(B20:B25,C20:C25)

But putting the address function in the slope calculation, like

=slope(ADDRESS(A2,2):B25,ADDRESS(A2,3):C25) does not work!

Why can't I combine these functions?

Is there another (simple) way?

Mayo

- Use address function to indicate range in calculation
- Is it possible to use address function in average function
- Use index function to shift range in a patterned formula
- Using ADDRESS function in AVERAGE
- Using SUM function with #N/A in some cells
- HOW TO: Use Advanced Filter to Exclude Records in Excel 2000
- Trouble Using InputBox Method to Select Range from Other Sheets
- Trouble Using InputBox Method to Select Range from Other Sheets
- Using Concatenate function to generate text in Text Box
- Using match() function to fetch row number from another worksheet
- Assign a Name to a Range in VBA
- Copy TextBox Value to a range in Excel
- Trouble Using InputBox Method to Select Range from Other Sheets
- Using Concatenate function to generate text in Text Box
- Using Different Functions to Consolidate Lists in excel 2007
- Refer to a Range in Headers/Footers
- How to select range in cycle?
- Linking to named ranges in another workbook
- Linking to named ranges in another workbook
- How to refer code to specific range in worksheet
- Using the ADDRESS function to refer to part of a range
- Using address function result in an array
- Using address function result in an array
- How do I use the address/match function to return an entire range reference - ie: C:C

calculation of a slope:

cell A2 contains the value 20

=ADDRESS(A2,2) returns $B$20

=ADDRESS(A2,3) returns $C$20

Cell B20 and C20 are the starting cells for calculating the slope:

=slope(B20:B25,C20:C25)

But putting the address function in the slope calculation, like

=slope(ADDRESS(A2,2):B25,ADDRESS(A2,3):C25) does not work!

Why can't I combine these functions?

Is there another (simple) way?

Mayo

--

Mayo

------------------------------------------------------------------------

Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371

View this thread: http://www.excelforum.com/showthread...hreadid=508986

I understand how to manipulate indirect & address function to get a

value of a cell.

However, I would like to get average value of a range instead of

the value of a cell.

I can use address(....) & ":" & address(...) to produce something

like $F$5:$F$10

However, average function does not take $F$5:$F$10 and calculate

the average value.

Is it possible to do so in excel without writing a VB macro?

Thanks

Tim

I have been playing around with a function all day and it is driving me nuts.

My goal is to automatically calculate the volatility of a warrant's (equity) price over time as it approaches the end of its term (in this case 60 months).

I want to create a formula that takes the STDEV of the Compounded Rate of Return and multiply that by the square root of the period (12 months). But I want to automate it somewhat by allowing me to copy the formula to other cells over time.

PROBLEM: For each new time period that I add, I need to subtract two periods off the backend of the calculation. So, the range essentially shifts down two, but increases by one as well at the bottom.

Example: See Attached Workbook

Range I11:I79 contains Rate of Return percentages over time

I want to calculate volatility as STDEV(PERIOD)*SQRT(12) every month until the warrant expires in 60 months.

In my example, you can see I have tried to use the following formula with some success in cell N72:

=STDEV(INDEX(_RR,COLUMNS(N72:$O72)):INDEX(_RR,COLUMNS(N72:$O72)+59))*SQRT(12)

where _RR is a dynamic named range referring to:

=OFFSET(Volatility!$I$13,0,0,COUNTA(Volatility!$I13:I80),1)

I need to be able to copy and paste this function (and have it automatically fill in the correct formula) in cells N12:N79. I also need to be able to add new entries to the end.

Thanks!

Any help is much appreciated.

I am wondering why this keeps giving me the VALUE error...

I am really stuck in solving this problem and would be grateful for any

help.

I have a column of cells in which some cells contain text, some values

and some the results of calculations. i.e.

B2 contains heading text

B3 contains A Name (The result of a VLOOKUP)

B4 contains 11452

B5 contains 12.05

B6 contains 8 (the result of a calculation)

B7 contains heading text

B8 is blank (awaiting result of a VLOOKUP)

B9 is blank (awaiting input)

B10 is blank (awaiting input)

B11 contains #N/A (Awaiting the result of a calc using data from B7,B8

and B())

This pattern continues in blocks of 5 cells to R63 in which I want to

use a function to total the contents of cells B6, B11, B16, B21 etc.

Since some of these cells contain calculated numeric values and some

contain #N/A (still awaiting their content to be calculated, as

sporting events take place) a SUM function or a =B6+B11+B16+B21 results

in another #N/A error.

Would be grateful to know how I can add this column of selected

non-adjacent cells in which there is a number containing #N/A. I need

the sum of the valid numbers without the need to be selective at the

time. I have approx 40 such columns needing a snap shot total every 15

mins.

with best regards

--

Jeeper

------------------------------------------------------------------------

Jeeper's Profile: http://www.excelforum.com/member.php...o&userid=24107

View this thread: http://www.excelforum.com/showthread...hreadid=377157

This step-by-step article explains how to use the ISNA and MATCH worksheet functions to exclude records in a Microsoft Excel database that do not match another list. How to Create ...

Hi,

I need to select a range of cells from a second workbook via a InputBox or similar.

I'm trying to do that with the following code:

VB:But I can't select a cell range if it is located in other workbook.InputBoxTest() Dim MySelection As Range Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8) MySelection.Select End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Can somenone point me to the right direction.

Thanks

P&V

Hi,

I need to select a range of cells from a second workbook via a InputBox

or similar.

I'm trying to do that with the following code:

Code:

--------------------

Sub InputBoxTest()

Dim MySelection As Range

Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)

MySelection.Select

End Sub

--------------------

But I can't select a cell range if it is located in other workbook.

Can somenone point me to the right direction.

Thanks

P&V

--

pao_e_vinho

------------------------------------------------------------------------

pao_e_vinho's Profile: http://www.excelforum.com/member.php...o&userid=21360

View this thread: http://www.excelforum.com/showthread...hreadid=554915

concatenating the values from two different cells (B4 and B6) on a specific

worksheet in my Excel file. I was able to use a reference to cell B4 to

generate the text for my text box using the following formula:

='SSTyp-AllD'!$H$4

However, when I try to enter either of the following formulas for the text

box text:

=concatenate('SSTyp-AllD'!$H$4, 'SSTyp-AllD'!$H$6)

='SSTyp-AllD'!$H$4 & 'SSTyp-AllD'!$H$6

I get the error message "The text you entered is not a valid reference or

defined name".

Is what I am attempting to do simply impossible in Excel? (I am running

Excel 2003 SP1). Or, is there some way to do this that I am missing?

--

Thanks,

Mary S.

I'm using match function to fetch row number from another worksheet (B). the formula is being used in worksheet (A).

The objective is to get the row number for a particular line (expense, income etc) which will then be used to get the value for that line and paste into a certain month using the Indirect function.

The problem is that in certain cases there is more than 1 line for the same item. E.g is in this link: http://www.flickr.com/photos/8886522@N05/5332955036/

I would like to fetch the row number of the subsequent instance(s) (rows 10 to 12 in attchment), if a previous one has already been accounted for (rows 5 to 7).

Any ideas? Many thanks in advance!

Sub mac2()

Dim r As Range

Set r = Cells(1, 1)

For i = 2 To 10

If Not IsEmpty(Cells(i, i).Value) Then

Set r = Union(r, Cells(i, i))

End If

Next

r.Select

ActiveWorkbook.Names.Add Name:="diagonal", RefersToR1C1:= ?????????

End Sub

By Naming a range I can make it available to worksheet functions.

--

Gary''s Student

Hi,

I need to select a range of cells from a second workbook via a InputBox or similar.

I'm trying to do that with the following code:

Sub InputBoxTest() Dim MySelection As Range Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8) MySelection.Select End SubBut I can't select a cell range if it is located in other workbook.

Can somenone point me to the right direction.

Thanks

P&V

concatenating the values from two different cells (B4 and B6) on a specific

worksheet in my Excel file. I was able to use a reference to cell B4 to

generate the text for my text box using the following formula:

='SSTyp-AllD'!$H$4

However, when I try to enter either of the following formulas for the text

box text:

=concatenate('SSTyp-AllD'!$H$4, 'SSTyp-AllD'!$H$6)

='SSTyp-AllD'!$H$4 & 'SSTyp-AllD'!$H$6

I get the error message "The text you entered is not a valid reference or

defined name".

Is what I am attempting to do simply impossible in Excel? (I am running

Excel 2003 SP1). Or, is there some way to do this that I am missing?

--

Thanks,

Mary S.

1. Follow instructions in to Consolidate Lists in tip

2. Select Data -> Consolidate (in Data Tools Group).

3. In Function, select Count and click OK.

4. Select cell A15.

5. Select Data -> Consolidate (in Data Tools Group).

6. In Function, select Average and click OK.

This code shows the week commencing date, can I put this in a header?

VB:If this way wont work, does anyone know how I can get the week commencing date in a header?Sheets(1).Range("A1") = Range("WCDate_2wk")If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Cheers

Kev

How to select range in cycle?

Thank you.

Sub search()

For i = 1 To 10

Rem I want to select range (not only a cell) in column 12 from row 1 to row i

Rem How to do rhis?

Range(1:i).Columns(12).Select

Selection.Copy

End Sub

Workbook A is a consolidation workbook that links to named ranges in

"source" Workbooks B, C, D, E....etc. There is a way to display all the named

ranges in the source workbooks and to select them by pointing, so as to

reduce the chance of formula typos. I just don't remember how. Help,

please!!!! and Thanks!

Workbook A is a consolidation workbook that links to named ranges in

"source" Workbooks B, C, D, E....etc. There is a way to display all the named

ranges in the source workbooks and to select them by pointing, so as to

reduce the chance of formula typos. I just don't remember how. Help,

please!!!! and Thanks!

How to refer to specific range in worksheet?

In my sheet1 Object I want to call method ToggleCutCopyAndPaste only for specific range.

this code works for whole sheet1.

Private Sub Worksheet_Activate()

Call ToggleCutCopyAndPaste(False)

End With

End Sub

=SUMIF(Main!C2:Main!C163,$A2,Main!I2:Main!I163)

What I would like is for the range (Main!C2:Main!C163) to be dynamic, i.e. to be able to replace this range with a reference that would be evaluated to "Main!C2:Main!C163". I've figured out how to create an expression that evaluates to the parts of the range (e.g. ** =ADDRESS(F1,3,1,TRUE,"Main") ** returns an acceptable Main:$C$163 reference), but it appears that Excel doesn't evaluate this before trying to use it; I always get an error. That is, if I try to formula:

=SUMIF(Main!C2:ADDRESS(F1,3,1,TRUE,"Main"),$A2,Main!I2:Main!I163)

I get an error. Is this simply a limitation of Excel? Is there someway to force a "pre-evaluation" of the ADDRESS part of the function (as there is in some programming languages)? Or is there some other way to accomplish this? I would think this would be doable else why would Excel explicitly include the ADDRESS function if I can't use the results of it (other than to visually look at it)?

Thanks for your help!

double quotes)

=ADDRESS(2,3,3)&":"&ADDRESS(2,4,2)

How do I use the result as a range reference within a function, array or by

external reference to the result?

Thanks in advance.

double quotes)

=ADDRESS(2,3,3)&":"&ADDRESS(2,4,2)

How do I use the result as a range reference within a function, array or by

external reference to the result?

Thanks in advance.

I can get the address function to return the reference of a column and a row: ie: $C$3

But I can't get a function that just says "hey it's in column $C:$C,

the goal is to have the sum range in my sum if/sumifs formulas not be static but instead be able to change based on whatever my lookup value is. So that way I can have a more dynamic formula and not worry about always making sure everything is in the same column or maintains the same column format

So match formula tells me the column a lookup is in, then i want the sumif formula to sum the range of that column. This would have many other applications though

See the attached excel file for a more detailed example of what i'm trying to do.

I would prefer a non array formula solution as i suck at array formulas at the moment

Thanks for any help