Can I create a recurrence formula in Excel e.g. A(N+1)=A(N)+2
So if e.g. A(1)=5, and N goes from 1 to 6, I will get a sequence as follows
Enter the first 2 cells, ie in A1: 5, in A2: 7
Then select A1:A2, copy* down to A6,
and Excel will fill the series as required
*drag the fill handle at the bottom right corner of A2
> So if e.g. A(1)=5, and N goes from 1 to 6, I will get a sequence as follows
"daibach" <email@example.com> skrev i en meddelelse
news:D232E8D4-28DC-4E35-A2C4-6964...formula question #27
I have an array of numbers like
say the range name is tab1
and an array of numbers like (call it tab2)
I want to look up the numbers from tab2 in tab1 and multilply it by the 2nd
column from tab1 and add up the result.
So, I want 150*.01+250*.015+350*.02
I tried as an array formula =sum((vlookup(tab2,tab1,2)*tab2)
but this does not work.
What should I be doing?
Say your tab2 info in col. A. In col. B type
vlookup(A1,tab1!$A$1:$B$5,2,TRUE) Note: Use "true" in the vlookup, since
your values in tab1 col. A and ...Finding MAX of a conditional range???
I would like to know if there is a way /set of formulas/ to find
MAX/MIN/ of a range that is defined by certain conditions. To be more
specific I am giving an example: I need to find MAX of range of numbers
/1, 3, 6, 8, etc./ and the range is defined by two dates. To each number
correspond a specific date. In other words, I would like to find MAX of
the range between two dates. I tried a combitanion of MAX and
SUMPRODUCT formulas, but no success.
Any help is welcome.
kras's Profile: ht...Plot Range ?
I am wondering if anyone can tell me a simple macro or way on workbook open
to flag when the plot range has been expanded ?
I have several workbooks which have macros that automatically update the
data in these workbooks and also update / expand the plot range of the charts
based on the data in the workbook. I am looking for a macro that can pop up a
message box letting me know when a plot range has been expanded since the
last time the workbook was opened.
...How to filter a coulered cell range?
I have a range that contain a coulored cells in red yellow and green (for
example), How can I fillter the yellow cells? I use office 2007
First turn on AutoFilter:
Office Button > Sort & Filter > Filter
The touch the pull-down and Filter by Color
Gary''s Student - gsnu201001
> I have a range that contain a coulored cells in red yellow and green (for
> example), How can I fillter the yellow cells? I use office 2007
Place the cursor in Header Row (i.e. row 1) and press Alt+D+F+F which will
apply the filter for your d...Referencing defined range
I have three named ranges (percentSales,percentMaterial,percentLabor). When
a user types into an account cell and wants to retrieve information for
percentSales, they would type "Sales". How can I take this input into a
function and refer to the percentSales range. I tried percent&"Sales", but
this is not working. It errors out because it is not refering to the range,
but just the text "percentSales". Below is an example of what I am doing.
What can I do to take the input and refer to the named range.
What I would like to happen
=INDEX(per...IF formula with multiple data range in cell
I'm trying to create an IF formula that returns data after searching a cell
that contains more than one data range. For ex:
Cell B3 contains GCVW-00001, GCVW-00002
I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need
the formula in D3 to return Yes if B3 contains GCVW-00002
I'm able to get the formula to return yes or no correctly if cell B3
contains either GCVW-00001 or GCVW-00002, but the formula won't work if it
contains both at the same time.
right now my basic formula looks like:
I want to transfer information from one spreadsheet to another with
different titles on the columns. How do I do this?
I want to direct the values on the column which has a different title
column to the sheet that I want to move it to.
> I want to transfer information from one spreadsheet to another with
> different titles on the columns. How do I do this?
> I want to direct the values on the column which has a different title
> column to the sheet that I want to move it to.
This is more of moving data to another sheet which has di...Excel variable Range
I receive a number of spreadsheets with different number
of rows. I want to copy a particular calc to a new column
for all rows. I can create a macro to do all this except
I do not know how to tell the macro to paste to the last
row which cam be different in each sheet. Any advice
LastRow can be calculated with
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Gerry" <firstname.lastname@example.org> wrote ...Entering Values and Updating Next Empty Cell in a Range
Please help, I hope there is a macro.
Every time I enter a value in cell j29, I would like it to post to the next
empty cell in range b139:b150. For example, if I enter 24 and it populates
b139, the next value I enter in j29 should post in b140
Thanks - Tom
You don't say what you want to do if row 150 is already filled.
This code will do what you want, and i have stopped it at B150.
Remove the stop if that is what you want
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
If Target.Address <> Range("J29").Address Then Exit Sub
lr ...Preserve number as text in Pivot table sum of values field
Does anyone know how I can create a table like this:
first column: second column: third column:
(product name) (product number) (sum of products)
I know how to create the first column (via "row labels") and third column
(via "sum values"). But for the second column, if I put the fields under
"row labels" the names get indented under the product name. If I put the
fields under "sum values" the names gets counted. I'd like to preserve the
product numbers as text. Thanks in advance fo...Embeded IF
Using Excel 3. I am trying to assign ABC codes to long list of products
based on % of sales.
A10 is part #, B10 is %. In C10, need formula to pick a code from following
table. The table changes periodically.
Code A=over 80%
Code B=between 60 and 80%
Code C=between 40...60%
Code D = less than 40%
>The table changes periodically.
Create a 2 column table like this...
Assume the table is in the range J2:K5
Then...this formula entered in C10 and copied down:
Microsoft Excel MVP
...Excel Column Formula
I have done this before..but I cannot remember how:
input a formula for an entire column (that calcs values individually
for each row), that takes different values for each calculation from a
ex: I have column a and b with numbers, and column c that I want the
summation of these values (individual rows).
Please help me out!
grandmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30503
View this thread: http://www.excelforum.com/showthread.php?threadi...Formula bar height in XL2K3 w/XP
With multi-line text (Alt-Enter) in the Formula Bar, it automagically extends
the height of the bar. This interferes with column actions since the bar now
obscures the column headings (A through ...). Painful when the 1st cell in a
column is multi-line.
Can this 'feature' be controlled?
Controlled ... like toggle off and on?
<Alt> + <V><F>
<View> and check - uncheck <FormulaBar>.
Please keep all correspondence within the Group, so all may benefit!
======================================...resizing a range
I can't the following code to work. I am trying to resize
a range so I can insert columns.
Dim range1 As Range
Dim range2 As Range
Set range1 = Worksheets("UnitTemplate").Range("CS7")
Set range2 = range1
Any help would be appreciated!
'Inserts 3 columns into worksheet at cell CS7
Selection.Enti...Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.
Dim rng As Range
Dim i As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 7 'InputBox("Enter Number of Columns Desired")
For i = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
I have a MASTER summary worksheet with this layout
07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total
No of late deliveries 36 40 20 10 9 115
No of Drivers 18 32 11 11 4 76
getting its info from WEEKLY worksheets eg
03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total
No of late deliveries 7 8 7 7 7 36
No of Drivers 4 3 1 6 4 18
I would like the master layout to be like :
No of late deliveries No of Drivers
07-Sep 36 18
14-Sep 40 32
21-Sep 20 11
28-Sep 10 11
05-Oct 9 4
Total 115 76
But as the values in the MASTER worksheet ...IF FORMULA need help
Please, I am trying really hard to work this out, but still I couldn't
come to result I want.
This is the example (invoices - paid, not paid...)
column A..................... column B...................... column C
DUE DATE....................PAID ON........................COMMENT
20.11.05...................(empty cell)......................not paid,
04.12.05...................(empty cell)......................not paid,
So, you...Countif formula between numbers
I'm have a bad day and can't figure this simple question out. I want Excel to
count a column of numbers and show the the total cells with values between
100 and 125.
The formula I have thus far is '=countif(H5:H125,">=125<=100")
why is this not working???
This has been asked about a dozen times today. Homework assingment?
=sumproduct((rngA>100)*(rngA<200)). modify to suit
"dandigger" <email@example.com> wrote in message
news:1D44A5E6-ECAB-441D-B9D8-409D23D0369E@microsof...How to assign range to Double array?
How can I assign the value of a range to a Double array?
The best I can do is a For Each loop. For example:
Function myIRR(myVal As Range, _
Optional myGuess As Double = 0.1)
Dim dVal() As Double, n as Long
ReDim dVal(1 To myVal.Rows.Count)
n = 0
For Each cell In myVal: n = n + 1: dVal(n) = cell: Next
myIRR = IRR(dVal, myGuess)
If dVal() were Variant, I could copy the range simply as follows:
Function myIRR(myVal As Range, _
Optional myGuess As Double = 0.1)
dVal = myVal
myIRR = IRR(dVal, myGuess)
But that results in a Type...Additional column formula in pivot table
I am a relative newbie to pivot tables. Using Excel 2003 I have, for example,
8 columns of quarterly financial data. I would like to add a 9th column to
calculate the CAGR for the 8 quarters. Can that be done within the pivot
How do I enter a formula to calculate a 7% sales tax?
If A1 holds the pre-tax price then =A1*7% will compute the sales tax while
=A1*1.7 will compute the price_with_tax-included.
Now all this is mathematically correct but we work in dollars and cents (or
pound and pennies etc.), so we need to do some rounding to the nearest cent
sales tax: =ROUND(A1*7%,2)
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"MissM07" <MissM07@discussions.microsoft.com> wrote in message
news:344FF6D4-...Want to find max in a range then return a name from a cell
The spreadsheet has 5 people each person has a list describing thei
Each has their highest sale, highest over cost sale etc.
I have a table with a describtion highest sale, highest over cost sal
I want the cell beside each title to calcute who has the highest figur
and place their name in this cell.
:confused: I have a spreadsheet with lists containing highest sale
highest average sale etc. What I want to do is create a formula tha
will find the max value within a list please note these values ar
spread out and do not run on the spreadsheet side by side or one aft...SUMPRODUCT between date range
I have successfully used SUMPRODUCT thanks to this discussion group but am
having a problem trying to capture a date range. Is it possibly the way I’m
entering the date? HELP! I woke up last night dreaming about this…
Date of Referral Homeless/At-Risk Received MV Services?
09/02/06 Homeless yes
09/15/06 Homeless yes
10/02/06 At-Risk yes
09/20/06 Homeless no
I need to capture the number of students fo...Multiple Ranges for a Chart
I am trying to use ranges from several pages in one chart. When I set the
source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3
I get the following error: Reference is not valid
HELP, what am I doing wrong?
Are you trying to use ranges from multiple sheets for the same X or Y series?
I've never seen that done. You can use X axis ranges from one sheet and
Y axis ranges from another.
> I am trying to use ranges from several pages in one chart. When I set the
> source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3
> I get the following error:...