Free Microsoft Excel 2013 Quick Reference

use address function to indicate range in calculation

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


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

--
Mayo
------------------------------------------------------------------------
Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371
View this thread: http://www.excelforum.com/showthread...hreadid=508986

Hi help needed

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

Hi all,

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 need to use the ADDRESS function to define a range from which an average can be determined. My current function is as follows:

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

Hi all

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

HOW TO: Use Advanced Filter to Exclude Records in Excel 2000

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 ...

Trouble Using InputBox Method to Select Range from Other Sheets

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:
	
 InputBoxTest() 
    Dim MySelection As Range 
    Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8) 
    MySelection.Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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

TROUBLE USING INPUTBOX METHOD TO SELECT RANGE FROM OTHER SHEETS

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

I would like to have a text box on my chart, whose text is generated by
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.

Hi,
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!

I would like to be able to assign a name to a range in VBA. For example:

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

I have a list of names in Column A, I want to use a textbox and a commandbutton to add new names to column a. I only need to now the command to copy the value in the textbox to a range in excel

Trouble Using InputBox Method to Select Range from Other Sheets

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

I would like to have a text box on my chart, whose text is generated by
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.

Is it possible to refer to a range in a header?

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


	VB:
	
 
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
If this way wont work, does anyone know how I can get the week commencing date in a header?

Cheers

Kev

Hi,
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

Someone told me how to do this put I have misplaced my notes:

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!

Someone told me how to do this put I have misplaced my notes:

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!

Hi,
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

I'm trying to create a dynamic range to be used in my formula. Here's a simplified version of one of the formulas I'm using:

=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!

The following formula returns "$C2$2" (Please note: result encapsulated in
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.

The following formula returns "$C2:D$2" (Please note: result encapsulated in
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 match function to tell me that something is in column 3

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