Free Microsoft Excel 2013 Quick Reference

Formula for incremental increase Results

I am trying to write a formula that will give each cutomer in my file 1
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

Hi,

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)

My first post! Great to be here... : D

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

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

Hi,

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

Hi guys,

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.

Here is an alternative method for incrementing a row reference when copying across columns. (See this forum's thread: http://www.ozgrid.com/forum/viewthread.php?tid=1530).

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

I have written the following code for cell CI4 of my spreadsheet:

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!

I have made an invoice on Excel. I want the Invoice Numbers to increase by
one each time. How do I do a formula for this?

Hello, I'm new to the forum and have a question about Excel. I have looked all over the web, but have failed in coming up with a solution. Below is my issue:

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.

Hello,

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'm trying to creat a formula that basically says if value in cell A2=$2.20 the value of A1=750,000. The next thing that I would like to do is if the value of A2 increases by $.01 then the value of A1 decreases buy 15,000. Conversly if A2 decreases by $.01 then i want A1 to increase by 15,000. I would like this to be automated based on what value is place in A2. Thanks for all the help.

I have a range of cells (A1:V25) with formulas in sheet 2 that refer to a specific cell in sheet 1. When I highlight the range of cells and drag the formulas down to (A26:V50) the corresponding cells all increase in increments of 25 when I only want increments of 1. For example, B1 in sheet 2 has a formula of "=sheet1!A60" but when I drag it down it ends up "=sheet1!A85" when I want it to be "=sheet1!A61". I realize B1 is now B26, but is there a formula I can use to only make it an increment of 1. Any help is appreciated.

Hello,
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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

John

Hi, thanks for reading this.

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!

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

[(Please don't delete this? ;-)]

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

Hi,

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

Looking for a formula (what if, vlookup, etc) that will give me the number of trades required for a given starting capital sum (B5), risk % (B6) inorder to approx. attain the figure shown in C17 (=> by up to £500). The compound trades based on incremental increases in trading capital are shown on the lookup table.

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

I have 2 formulas in 2 rows and when I copy them down I only want each formula to increase by 1.

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!