Free Microsoft Excel 2013 Quick Reference

- Formula for incremental increase
- Calculating Incremental Increase for Different Values
- Incremental increase in value between two known values, over a time period
- Creating a formula for periodic increases along a sloped length
- Formula to add nth cell of from multiple sheets
- Increment Row Reference By X Each Row
- Increment by row when copying across columns (alternative me
- Copying VBA code to next cell and "auto-increment" cell?
- How do I input for an automatic increment in a cell on Excel?
- How to copy formula and it following a pattern?
- Cannot Increase Cell Reference Amount by Multiple When Using Fill Handle
- Looking for a Formula for conditional incremental changes
- Formulas in increments of 1 for a range of cells
- Increasing chart range incrementally for each series
- If profit is less than x then increase rrp until profit reaches n
- Incremental date increase from previous sheet
- Increment Part Criteria Of Formula When Dragging/Copying
- Formula To Group Series Of Data
- Complex vlookup or If/Then stmt
- Increment Formula Row Reference By n Every X Rows

point for every 3,000 of product that they purchase up to 5 total

points. I have tried many variations on IF statments but am unable to

get them correctly recognize each level. Any assistance that anyone

could provide would be greatly appreciated!

-A

--

alexrm

here is what I need to do:

I have 3 data ranges (3 columns) which represent # of items sold

1-10 11-20 21-30

the multipliers in each range are as follows:

1-10 = X1 11-20 = X1.5 21-30 = X2 cell "B4" is where I put the #items

Here are the 3 formulas I have for each range:

1-10: =IF(((B4)>=1)*AND((B4)=11)*AND((B4)=21)*AND((B4)

As shown in the below example:

Column B - F represents the years 2010 - 2014, respectively.

For 2010, I have $25 and for 2014 I have $100.

For 2011, 2012, 2013, I would like to use a formula to create the incremental increase in value over those years. Assume that the percentage of increase would be the same each year respectively.

Can you help?

Col B 2010 = $25

Col C 2011 = ?

Col D 2012 = ?

Col E 2013 = ?

Col F 2014 = $100

I have a function I'd like to create but I'm not sure how to do it.

I am calculating the length of studs in a new residential building. Assuming a stud per foot perimeter including waste, the calculation is easy enough. I've subtracted my gable end walls (the walls with a peak above, aka, two small triangles), so I'm left with only an 8' stud quantity.

I would like to add in my sloping studs.

Assuming a 30' length at a slope of 10/12 and 8' walls, this will give me a height of 12.50' in the gable only, a run of 15', and a sloped length of 19.80'.

12.50 / 15 = 0.83 approximately.

So my formula would be the 8' height + 0.83' / foot until the peak of 20.50' is reached, and then mirrored again to go down the gable to the other end of the 30' wall.

I need the quantities separated by stud lengths (8-10', 10-12', 12-14', etc in 2' increments up to 24')

I would rather have this integrated into my main stud formula, though adding the values to a chart so I can manually add them where appropriate would not be so bad either.

I've included my excel file. The calculation falls under any of the exterior stud calculations, with references to sheet 2, B55:60.

Thanks for any help

I created a formula for a cell which adds values from the same cell of different sheets.

When I drag the formula along the row it changes the cell references normally (ie reference the adjacent sheets). - This is what I want.

However if i drag the formula downwards i want the cell references to jump every 107 rows.

I have created the formula in the 1st cell. Dragged it along the row to fill the row with the formula

Then Im dragging the formula down and doing an edit >replace for the formula by changing the cell number with the cell number i want (which is a fixed increment for each row i go down- increase row by 107).

I would like a formula which would enable me to drag the summing formula both ways and still do its job.

Offset with Row seems to to too cumbersome as I have to refer 12 cells from 12 sheets in the formula.

For reference the formulla in cell 1 is:

=E1SLD!D106+E2LAY!D106+E3TSP!D106+E4LOD!D106+E5ERT!D106+E6EGA!D106+E7AUX!D106

+E8SUP!D106+E9MDG!D106+E10ASB!D106+E11CAD!D106+E12SPL!D106

For the cell below it, it is:

=E1SLD!D213+E2LAY!D213+E3TSP!D213+E4LOD!D213+E5ERT!D213+E6EGA!D213+E7AUX!D213

+E8SUP!D213+E9MDG!D213+E10ASB!D213+E11CAD!D213+E12SPL!D213

P.S: Unable to upload the excel file as its close to 2MB (zipped).

I had more of an excel questions.

Let's say

A1 "=D1"

A2 "=D4"

A3 "=D7"

so baiscally the row reference increases by 3. How could I write a cell formula for A1 and so that I can just drag and drop it down for the rest of the cells?

Thanks.

Use the OFFSET function to achieve similar results by placing this formula in column D and copying across (as needed):

=SUM(OFFSET($A$1,0,0,COLUMN()-2,1))

The effect is such that at each column to the right of the previous one, the row number increases by 1, and just like the thread mentioned above, will achieve the same results as if you had entered the following:

=SUM($A$1:$A$2) at Column D

=SUM($A$1:$A$3) at Column E

=SUM($A$1:$A$4) at Column F, et cetera.

The values returned are the same as those using the INDIRECT/ADDRESS formula, but this OFFSET function formula is a little shorter (and probably a little faster when dealing with hundreds of formulas, although I haven't tested this).

Regards,

Tom

Sub Auto_Open()

If Range("BH4").Value = "6 CD Changer" And Range("BI4").Value = "Y" Then

Range("CI4").Value = "8"

ElseIf Range("BH4").Value = "6 CD Changer" And Range("BI4").Value = "-" Then

Range("CI4").Value = "7"

ElseIf Range("BH4").Value = "Navigation" Then

Range("CI4").Value = "N"

ElseIf Range("BH4").Value = "Single CD" And Range("BI4").Value = "Y" Then

Range("CI4").Value = "5"

ElseIf Range("BH4").Value = "Single CD" And Range("BI4").Value = "-" Then

Range("CI4").Value = "4"

ElseIf Range("BH4").Value = "AM/FM" And Range("L4").Value = "C/C" And Range("L4").Value = "S/C" Then

Range("CI4").Value = "2"

ElseIf Range("BH4").Value = "AM/FM" And Range("L4").Value = "S/C" Then

Range("CI4").Value = "1"

Else

Range("CI4").Value = "ERROR"

End If

End Sub

The problem is I need to use the same formula again for about 250+ more rows and also increase the cell by one. The next cell (CI5) needs to have the same code just incremented by one. For instance:

Sub Auto_Open()

If Range("BH5").Value = "6 CD Changer" And Range("BI5").Value = "Y" Then

Range("CI5").Value = "8"

ElseIf Range("BH5").Value = "6 CD Changer" And Range("BI5").Value = "-" Then

Range("CI5").Value = "7"

ElseIf Range("BH5").Value = "Navigation" Then

Range("CI5").Value = "N"

ElseIf Range("BH5").Value = "Single CD" And Range("BI5").Value = "Y" Then

Range("CI5").Value = "5"

ElseIf Range("BH5").Value = "Single CD" And Range("BI5").Value = "-" Then

Range("CI5").Value = "4"

ElseIf Range("BH5").Value = "AM/FM" And Range("L5").Value = "C/C" And Range("L5").Value = "S/C" Then

Range("CI5").Value = "2"

ElseIf Range("BH5").Value = "AM/FM" And Range("L5").Value = "S/C" Then

Range("CI5").Value = "1"

Else

Range("CI5").Value = "ERROR"

End If

End Sub

It is the same principle of Excel automatically changing your formula for you when you copy and paste, I just don't know how to do it in VBA. Please tell me I don't have to change all the statements manually. Any help would be greatly appreciated. Thank you!

one each time. How do I do a formula for this?

I'm working with one worksheet that has two sheets,named PAGE 1 and PAGE 2. PAGE 1> column B contains dollar amounts for Gross Revenue starting at cell B131 and ending on cell B147.

PAGE 2 has a row in which three of the cells from PAGE 1 are added, for example B131-133. On the same row, but the next column, the same formula is entered but with each cell incrementing by one. Below is the formulas to show you what I mean.

='PAGE 1'!$B133+'PAGE 1'!$B134+'PAGE 1'!$B135

='PAGE 1'!$B134+'PAGE 1'!$B135+'PAGE 1'!$B136

As you can see, on the same row, but next column, the formula for the sum is increased by one cell each time.

What I'm trying to do is be able to copy this formula over to the next column, but within the same row, and following this pattern.

I've read and tried the OFFSET function, but I cannot get it to work.

Any help will be appreciated it. Thank you.

I'm trying to do the following on the attached worksheet:

Have an Averaging formula for cumulative averaging (M-column) that I can copy down using the fill handle. Each new row of M-column will reference all of the same data in E-column, plus 4 more rows.

Basically the first averaging formula may be unique, but each successive copy of the Average will reference all the the data in E-column, plus the next 4 cells.

If you see what I've done so far, I've manually created each average formula like this:

= ((AVERAGE(E2:E5))^2)

= ((AVERAGE(E2:E9))^2)

= ((AVERAGE(E2:E13))^2)

= ((AVERAGE(E2:E17))^2)

= ((AVERAGE(E2:E21))^2)

Etc...

I would like to not have to manually increment each of the 4 additional cells referenced, but am having trouble trying to figure this out with either Offset or Indirect, which I'm not entirely sure are appropriate anyway.

Thanks for your time in advance.

I inherited some code that is used to increase the range by one column (on both the upper and lower bound of the data) for each series in whatever bar chart is selected on a sheet.

In most instances this works fine, if the range of data is through a rolling period. However, I'd like to modify the code to increase the range of the upper limit but still keep the lower limit constant (i.e. say the original range is $B$2:$S$2, after running the code I would want the range to be $B$2:$T$2 for that series. If run again it would be $B$2:$U$2 for that series and so on...)

Here is the code. Basically a function 'GetChartRange' is called twice to grab the range for x and y values in the selected chart, the range is returned and then increased using Offset. I think its a case of modifying the 2 statements with .Offset, but Im not sure how I would change them to only increase the upper limit? Any help would be appreciated.

VB:Increase_Chart_Range() Dim mySrs As Series Set MyChart = ActiveChart For Each mySrs In ActiveChart.SeriesCollection Set xv = GetChartRange(MyChart, mySrs.PlotOrder, "xvalues") Set V = GetChartRange(MyChart, mySrs.PlotOrder, "values") ValuesRange = V.Address XValuesRange = xv.Address MyChart.SeriesCollection(mySrs.PlotOrder).Values = V.Offset(0, 1) MyChart.SeriesCollection(mySrs.PlotOrder).XValues = xv.Offset(0, 1) Next mySrs End Sub Function GetChartRange(cht, mySrs, ValOrX) As Range Dim Sf As String Dim CommaCnt As Integer Dim Commas() As Integer Dim ListSep As String * 1 Dim Temp As Variant Set GetChartRange = Nothing On Error Resume Next Sf = cht.SeriesCollection(mySrs).Formula ' Check for noncontiguous ranges by counting commas ' Also, store the character position of the commas CommaCnt = 0 ListSep = Application.International(xlListSeparator) For i = 1 To Len(Sf) If Mid(Sf, i, 1) = ListSep Then CommaCnt = CommaCnt + 1 Redim Preserve Commas(CommaCnt) Commas(CommaCnt) = i End If Next i If CommaCnt > 3 Then Exit Function Select Case UCase(ValOrX) Case "XVALUES" Temp = Mid(Sf, Commas(1) + 1, Commas(2) - Commas(1) - 1) Set GetChartRange = Range(Temp) Case "VALUES" Temp = Mid(Sf, Commas(2) + 1, Commas(3) - Commas(2) - 1) Set GetChartRange = Range(Temp) End Select End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

John

I have a forecast sheet for my product inventory. Some products do not make any profit at the current RRP, due to other costs/fees offset against them.

I have already written a formula which automatically calculates the profit once the rrp has been entered into the relevant cell.

I wish to set a minimum profit of 50p on all items that are currently earning less than 50p. So what I'm trying to accomplish is a formula that will be something along these lines.

If (profit) is less than 0.50 then increase (rrp) until (profit) reaches 0.50 (I can finish off the value if false myself).

I thought maybe to increment rrp by 0.01p until 50p is reached, but then I have no idea how to do this.

I tried to be as thorough as possible, I hope it's not too confused a question!

Was hoping someone could advise a formula, UDF or VBA for cell B3 to increase date by 7 days on every new sheet?

As per this screenshot,

I am trying to work out a method which will allow me to drag the forumula which is in cell B13 from B13:B17, and have in increase the date serial number by one for each row by which it descends.

In other words I want to drag the formula down to, say B14, and have it increase the date serial to = that which is in cell A14.

Any ideas?

[the obvious benefit of being able to do this is that I don't have to manually adjust the date serial in each cell on each new row]

ta

I would be grateful of any help anyone could provide. I need a bit of help adapting a formula which I was provided on the forum earlier in the week (I have attached a worksheet as an example). The formula is in column A in the following format ‘=IF(B4=B3,A3,IF(OR(AND(B3=B4)),A3+1,A3))’.

It is used to number groups of data and it should increase by an increment of 1 for each group. The groups are defined by a changing angle (column B) which runs from high to low (group 1) then low to high (group 2) etc.

The formula in column A works fine until the angle column does not change for 1 or more data points (which sometimes occurs on the change over between groups). See row 430 and 331, the group 6 needs to either be included in group 5 or 7 and then the current group 7 needs to become group 6 and carry on as normal. Is there any way of adapting this formula or changing it to account for this problem?

I have a lot of data series like these and the repeated angles could occur between any groups not just at group 6 as it has done in this example.

I hope this makes sense.

Many thanks in advance.

Sam

ie something that will (depending on which ROT I am looking at Min, Max, Average) will look up the column in the look up table and then when the condition above is met insert the corresponding trades figure in the table F, H, J 9-10.

Thanks for your help and/or suggestions.

Murray Howe

Simple example: This is what I get by copying row 1 & 2 and pasting in row 3 & 4

row1: =A1+B1

row2: =E1

row3: =A3+B3

row4: =E3

I am copying Row 1 & 2 (as a group) to Row 3 & 4 (and so on), but instead adding 2 rows to each formula, I want it to increase by only one.

This is what I want:

row1: =A1+B1

row2: =E1

row3: =A2+B2

row4: =E2

I could probably do it in code, but it is for a user and I would like to find out if there is a way I could direct the user to do it herself. Is there a way to change the increase or "trick" Excel into doing what I need?

THANKS!