Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Iterations of complex calculations

In Excel 2000, I have a complex calculation (in a worksheet) that takes 4
parameters and uses them in 4 different sub-calculations, involving
relative/conditional sub-sub-calculations. In effect, it's a model.

Having done this, I now want to pour a series of numbers into this model, to
see its effect on various combinations of parameters.

Rather than type each parameter in, copy-paste-value the result into a
summary table, how can I get Excel to run the model on call from a worksheet?

I am illiterate in VisualBasic, so anything that avoids macroes would be cool.


Post your answer or comment

comments powered by Disqus
Hello,

I need to call a sub with each time my Data Tables calculate.

I use a macro to copy and paste a cell's value from one sheet to another so that I can create a few data tables. The way the data tables (under What-if Analysis) are working together is causing some problems as the cell that is being copied and pasted is not updating as the various data tables run. Is there a way to call the basic copy and paste macro with each iteration of the Data Table run?

Thank you.

Hello!

I hope you can help me with the following task:

I have three workbooks called DATA, PROCESSOR and RESULTS.

DATA contains the input data that will be processed in the PROCESSOR workbook while the RESULTS will aggregate the results from the PROCESSOR workbook. The DATA workbook links to the PROCESSOR workbook.

I need a macro that will cycle through worksheet names (V1 on INPUT tab in the DATA workbook) – for each value in the V column of the INPUT worksheet of the DATA workbook this macro will recalculate the PROCESSOR workbook and copy the A to O columns from its Results worksheet to the worksheet of the RESULTS workbook whose name is currently selected in T1 on the INPUT worksheet of the DATA workbook.

Example cycle:

1) Set “1” to T1 on INPUT tab of DATA
2) Recalculate Processor (should be calculated only at this stage as it takes a long to calculate in the full variant)
3) Copy A to O columns from the RESULTS tab of PROCESSOR worksheet
4) Paste data into the “1” tab of the RESULTS workbook.
5) Continue to the next number in the list of the numbers in the V column on INPUT tab of DATA

I need this prototype macro to aggregate the results of the calculation on a large data set consisting of multiple data subsets.

I have attached all 3 workbooks.

I will be happy to hear what you think!)

Dima

Hi All,

I have read through this forum regarding this topic and have had no luck implementing a fix. What I need is, that at the end of each iteration of a solver instance, a macro is called. Each iteration should call the function "Macro1". As it is now, it appears that the solver is called, but the macro is not. My code is below:

Function Macro1()
Range("aq2").GoalSeek Goal:=0, ChangingCell:=Range("target")
Range("target").Copy
Range("as2").PasteSpecial Paste:=xlPasteValues
End Function

Sub SolveForSituations()
Dim sRangeName As String, sRangeAddress As String
Dim nResult As Long, nCalcMode As Long
sRangeName = "EscenariosSolverByChange"
sRangeAddress = Range(sRangeName).Address
Range("SolverResult").Clear
Range("ag2").Clear
Range("ai2").Clear
Range(sRangeName).Value = 25
solverReset
SolverOk SetCell:=Range("SolverTarget").Address, MaxMinVal:=2, ValueOf:="0", ByChange:= _
Range("EscenariosSolverByChange").Address
SolverAdd CellRef:=sRangeAddress, Relation:=1, FormulaText:="75"
SolverAdd CellRef:=sRangeAddress, Relation:=3, FormulaText:="-75"
SolverOptions StepThru:=True
SolverSolve UserFinish:=True, ShowRef:=Macro1
End Sub

XL: Examples of Interest Calculations Outside the United States

Microsoft financial functions presume customary financial practices in the United States. Other countries have different laws about quoting and computing interest. This article ...

Dear Gurus,

Is this possible to use pivot to present text data instead of value calculation e.g count, sum,%... in data area?
For example:
MOnth Jan Feb Mar
AAA USA AUS POL
BBB POL AUS USA
CCC BRI USA POL

Best regards,
TQV

Hii,
I am an average good user of excel, till now I have been able to solve all kind of complex calculations some way but I have encountered a situation & wondering if there is any way to do this in excel or using some VBA. I would be grateful to all of you for this.

My excel sheet is some thing like this

Order No Require Item Require Qty Stock Status
10001 XXXXX 100 300 200
10001 YYYYY 100 200 100
10002 XXXXX 200 300 200
10002 YYYYY 200 200 100
10003 XXXXX 100 300 100
10003 YYYYY 100 100 000

There are a lot of different orders for different different products but all have some common ingredients. On any given day If I see my pending orders & Inventory then it looks like above & showing all stock available but I can't process all my orders as stock will be reduced after first order, then I wont be able to process second order but I can process 3rd one . What I want to do is process this data some way that It takes the first order & if all the material is available then reduce the stock so in above example after first order XXXXX will be left 200 & YYYYY 100. Then for next order XXXXX is sufficient but YYYYY is not available so no stock shall be deducted & for third order calculation also XXXXX will be 200 & YYYYY will be 100 & if there is any next order then for that XXXXX = 100 & YYYYY = 0 . Then show it in next column.

I don't think it's possible without VBA but I am not much familiar with that other than copy & pasting the codes from web & modifying here & there. But an expert might do it with excel also.. (I will appriciate if any1 can give me a VBA code ) This is just example in actual my data is 20000 or more row with a lot of product combinations.

Thanking you all in advance for your input & support

Hi again

I have workbooks in which summary tables are generated by lookup formulas. Tables are on different sheets but all use the same lookup value by referring to a cell on the 'master' sheet. The lookup value appears in cells on all sheets, by reference to the master sheet (e.g. [formula] = mastersheet!$B$2).

The master sheet contains the main summary table and is copied and detached for distribution, using a macro. For practical reasons, this is done in two stages, first copying the sheet within the workbook (to make minor alterations), then moving it to a new book to save and distribute.

This is where the problem arises. After the sheet has been detached, we find that if we now change the lookup value on the master sheet in the original file, the tables on that sheet will update normally, but the cells on other sheets remain frozen at the previous value and the tables on those sheets do not update.

The only way round the problem is by Shift + Ctrl + Alt + F9. (Maybe I should add that all my workbooks are always set to automatic calculation.)

The macro itself is not the cause of the problem; if we follow the same procedure manually, the result is the same. However, if we move the sheet in one step, eliminating the intermediate copying stage, the problem does not arise. But this is evading the problem, not solving it, and I would be reluctant to have to resort to this.

The original problem remains as stated, viz. failure of automatic calculation.

Any help appreciated.

hi to all!

i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..

buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..

i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula
"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..

so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.. pls. help me...thanks!

i want to add the sample data in this thread but i dont know how to add the file.

Hi
I'm trying to do a sum of squares calculation, between and within groups, across multiple columns.
I llike this to occur automatically when I input the data. Where I've running into difficulty is calculating the mean of each group(without sorting and manually selecting). See sample of data below:
Typestdby_currentrx_currentNormal0.141116170.171218Rework0.140827160.170686Normal0.140360580.172524Rework0.136112870.167756Normal0.141427740.170232
Note that there are more columns of different parameters I've just inserted two for demonstation
Any help appreciated.
Thanks
Ed

Help please !

I have to calculate redundancy entitlements based on length of service
calculated in completed calender months. DATEDIF(Date1,Date2,"m")
works fine 99.9% of the time except that it ignores the days in the
dates. For this calculation an employee is deemed to have completed a
months service if the days of the month are inclusive.

For example; 22/03/2007 to 22/05/2007 is clearly two months service,
but so is 22/03/2007 to 21/05/2007 because they are deemed to have
completed the month, whereas 22/03/2007 to 20/05/2007 is only one
completed month. ( I didn't make the rule. That's how it is, )

So now I have =IF(((DAY(A10))-1)=(DAY(B10)),DATEDIF(A10,B10,"m")+1,
(DATEDIF(A10,B10,"m"))) but this doesn't work if the number of days in
the month of the second date is greater than the number of days in the
month of the first date.

I'm going crazy trying to work this out and I owe it to these people
that the entitlements are 100% accurate. Any help would be much
appreciated!

Dave

Imagine the following:

Schedule I
A6 = 8006-001
A7 = 9080-002
A8 = xxxx
etc.

Schedule K - 2008 Final
D12 = 8009-001
B31 = TOTAL COSTS
I31 = $9,000

The above portion in bold on Schedule K repeats but the different values in the first and last cells. The middle cell will always say 'TOTAL COSTS.' This is where I am getting stuck on.

In short, I am starting with cell A6 on Schedule I (this is the 'project' object variable in the first for each loop), taking that cell and going to Schedule K and running it down column D (using 'b' as the object variable for the second for each loop) to compare for a match. If a match is found inside of this second/nested for each loop, then I want to use the FIND method and move over 2 columns to the left, and then down that column (B) to locate the cell that contains the next occurence of the letters 'TOTAL COSTS.' Then from 'TOTAL COSTS' I want to move over 7 cells, get the value in that cell, and drop it in a cell on the first tab, Schedule I.

The problem is that on the first iteration of the nested for each loop, the program is getting stuck on the object variable 'findTotalCost.' Then comes across and extracts $9,000 and pastes it on the Schedule I. This is ok for the first iteration. However the $9,000 value gets pasted again in the next cell below, again and again with each iteration.

So the program is stuck on the first occurence of 'TOTAL COSTS.' I need to somehow clear the value of 'TOTAL COSTS' at the of each iteration. Al least that's what I think. That way each occurence of 'TOTAL COSTS' will be picked up not just the first one.

Please help.

Sub billable2008()

    Dim b As Range
    Dim project As Range
    Dim findTotalCost As Range
    
    r = 6
       
    For Each project In Worksheets("Schedule I").Range("A:A")
        For Each b In Worksheets("Schedule K - 2008 Final").Range("D:D")
            If b.Value = project.Value Then
                Set findTotalCost = Columns(2).Find(what:="TOTAL COSTS")
                Worksheets("Schedule I").Cells(r, 28).Value = findTotalCost.Offset(0, 19).Value
            Exit For
            End If
        Next b
        r = r + 1
    Next project
End Sub


Hello all,

I have a VBA routine that on each loop updates data from queries, prepares some formatting and then prints to pdf. This all works fine, the problem is that it starts off and produces each pdf in around 30 seconds but on each iteration this gets larger and larger (and there are 700 schools/iterations we need to produce the report for) in the end it slows Excel right down practically to a halt and I have to close down and reopen Excel - then everything is fast again.

Anyway I've narrowed the problem down to this function that changes a chart series and adjusts the scale depending on certain criteria. This function seems to be the one that causes the loop to take longer and longer. Has anyone any ideas what it could be??

Note that I've declared some worksheet types as module level public vars now to try and speed things up so these vars are declared at top of module (and so get declared only once during cycle instead of getting declared on every iteration of the loop)

Many thanks for any help:

Public Sub AmendCharts(sType As
SchoolType)
' Sorts out NOR Series for different phases
' S Barker Jul 2008
' Updated: Sept 08 - Now includes code to sort SEN Scales depending on School Type - S Barker Sept 2008

Set wks = Sheets("Disp_PupChars01")

Dim ColFrom As Byte
Dim ColTo As Byte
Dim StartCol As Byte

StartCol = Range("NORYearGroupStart").Column

Select Case sType
    Case Nursery:
        ColFrom = StartCol
        ColTo = StartCol + 1
    Case SpecialNursery:
        ColFrom = StartCol
        ColTo = StartCol + 1
    Case Primary:
        ColFrom = StartCol + 2
        ColTo = StartCol + 8 '27
    Case SpecialPrimary:
        ColFrom = StartCol + 2
        ColTo = StartCol + 8 '27
    Case Secondary:
        ColFrom = StartCol + 9 '28
        ColTo = StartCol + 13 '32
    Case SpecialSecondary:
        ColFrom = StartCol + 9 '28
        ColTo = StartCol + 13 '32
    Case Sixth:
        ColFrom = StartCol + 14 '33
        ColTo = StartCol + 15 '34
    Case SpecialSixthForm: ' Include yr 14 for special sixth forms
        ColFrom = StartCol + 14
        ColTo = StartCol + 16
End Select

'wks.ChartObjects("Chart 4").Activate
'ActiveChart.PlotArea.Select
wks.ChartObjects("Chart 4").Chart.SeriesCollection(1).Values = _
    "='Disp_PupChars - Table Sheet'!R14C" & ColFrom & ":R14C" & ColTo
wks.ChartObjects("Chart 4").Chart.SeriesCollection(2).Values = _
    "='Disp_PupChars - Table Sheet'!R15C" & ColFrom & ":R15C" & ColTo
wks.ChartObjects("Chart 4").Chart.SeriesCollection(1).XValues = _
    "='Disp_PupChars - Table Sheet'!R12C" & ColFrom & ":R12C" & ColTo
wks.ChartObjects("Chart 4").Chart.SeriesCollection(2).XValues = _
    "='Disp_PupChars - Table Sheet'!R12C" & ColFrom & ":R12C" & ColTo

Set wks = Nothing

' Updated code to now sort the SEN scales out depending on phase
' S Barker Sept 08

Dim ScaleRange As Long, x As Byte

Select Case sType
    Case Nursery: ScaleRange = 70
    Case Primary: ScaleRange = 80
    Case Secondary: ScaleRange = 60
    Case Sixth: ScaleRange = 30
    Case Else: ScaleRange = 100
End Select

Set SEN_Sht = Sheets(SENSht)

' Do for each of the 4 charts..
For x = 1 To 4
    Set SEN_Chart = SEN_Sht.ChartObjects("Chart " & x)
    SEN_Chart.Chart.Axes(xlValue).MaximumScale = ScaleRange
    Set SEN_Chart = Nothing
Next x

Set SEN_Sht = Nothing

End Sub
For info this is an excerpt of what is declared at the top of the module

Public wks As Worksheet
Public SEN_Sht As Worksheet
Public SEN_Chart As ChartObject

Public Enum SchoolType
    Nursery = 1
    Primary = 2
    Secondary = 3
    Special = 4
    Sixth = 5
    SpecialNursery = 6
    SpecialPrimary = 7
    SpecialSecondary = 8
    SpecialSixthForm = 9
End Enum


Our company produces financial statements for clients using an Excel workbook
that has numerous worksheets relating to financial years. Each sheet is
populated by extracting data from our SUN (Systems Union) accounting
application using their 'Vision XL' add-in. VBA macros do a lot of the grunt
work and have numerous variables, including some relating to named ranges
that are referenced in the multitude of formulae within each sheet.
While these financial workbooks have been working fine over the last years,
our client information has grown and obviously financial years are added
annually and the workboks for some of our clients have become rather large.
My management is concerned whether there could be any limitations in
Excel/VBA that might cause them to stop working or give unpredictable results.
Does anyone know of any limits on the number of variables / calculations (or
combination thereof) that we should be wary of?
Many thanks in advance.

Keith Carter

I'm just going to use a basic example here, but this is actually for a piece of work I am doing.

Say, I have choose cell B4 to give the sum of B1 and B2. So, the formula for cell B4 is =B1+B2.

I will change B1 and B2 sometimes, so B4 will change along with them.

I want a list of cells giving me a history of the calculations B4 has performed.

So for example, I'd like C1:C10 to each automatically contain a history of B4's results, with the oldest being C1 and the most recent being C10.

Does anyone know how to do this?

~ Ado

Has anyone come across a formula that can calculate the weeks of supply (WOS)
for a production scheduling application? For the data:

Week 1 2 3 4 5
Production 5 3 1 1 1
Sales 0 2 3 2 1
Inventory 5 6 4 3 3
WOS 2 3 99 99 99

The trick is to develop a formula that takes the week 1 ending inventory of
5 and then subtracts sales of 2 in week 2, then subtracts sales of 3 in week
3, and upon realizing that at week 3 the inventory is <=0 you take [week] 3
minus [week] 1 to get 2 weeks of supply. The same calculation repeats for
week 2. For week three 99 is indicated to show the future sales listed won't
deplete the inventory.

Does anyone know of a way to calculate this without averaging the sales or
setting up an akward cumulative calculation for each week and using the match
and index functions?

This weeks of supply calculation has a number of applications. I suprised
there isn't a standard function in Excel with functionality along the lines
of "this value will reach zero in x periods with the irregular series of
withdrawls in range b1..m1"

To all --
I have two columns of numbers, and I would like to divide the 1st of each
pair by the 2nd of the pair, and get the average of those calculations. Kind
of addressing "the sum of the averages is different from the average of the
sums" issue. So my data might look like this:

A B
1 10 4
2 11 3
3 15 6

etc

If my brain works right, the average of dividing 10/4, 11/3, & 15/6 would be
2.89. Is this a number I can get with some sort of CSE formula?

Thank you in advance.

In the column I have both user entered and calculated values, how do I sum
the values of non calculated cells...?

Hi, I am new to this site and was hoping somebody could help me with the following. I am attempting to automatically populate a cell with the result of a calculation. For example, Column A will be populated with a user-entered date. Column B contains a formula which works out the date difference between the user-entered date (in Column A) and a fixed date (in years, months, days format). What I am struggling to accomplish is to have Column B automatically populated with the date difference as soon as return is pressed after Column A has been populated. Any advice would be greatly appreciated. Thank you.

Hello!

I hope you can help me with the following task:

I have three workbooks called DATA, PROCESSOR and RESULTS.

DATA contains the input data that will be processed in the PROCESSOR workbook while the RESULTS will aggregate the results from the PROCESSOR workbook. The DATA workbook links to the PROCESSOR workbook.

I need a macro that will cycle through worksheet names (V1 on INPUT tab in the DATA workbook) – for each value in the V column of the INPUT worksheet of the DATA workbook this macro will recalculate the PROCESSOR workbook and copy the A to O columns from its Results worksheet to the worksheet of the RESULTS workbook whose name is currently selected in T1 on the INPUT worksheet of the DATA workbook.

Example cycle:

1) Set “1” to T1 on INPUT tab of DATA
2) Recalculate Processor (should be calculated only at this stage as it takes a long to calculate in the full variant)
3) Copy A to O columns from the RESULTS tab of PROCESSOR worksheet
4) Paste data into the “1” tab of the RESULTS workbook.
5) Continue to the next number in the list of the numbers in the V column on INPUT tab of DATA

I need this prototype macro to aggregate the results of the calculation on a large data set consisting of multiple data subsets.

I have attached all 3 workbooks.

I will be happy to hear what you think!)

Dima

Hi,

I am trying to generate a chart which would have:
- On the X axis, the "parameter": all the values included in a drop-down list (created with data validation)
- On the Y axis, the "results": the values taken by another cell (the content of the cell changes when the parameter is changed from the drop-down list).

In the attachment, please make as if the "SupportSheet" worksheet did not exist, and assume you only have the "Chart" worksheet. My issue is that the result is generated from a series of complex calculations from a different file, so it is not trivial to create a table with all the values to be charted.

I am using Excel 2002 (sic), but could get hold of a more recent version if needed.

Thanks in advance for your help!

Hii,
I am an average good user of excel, till now I have been able to solve all kind of complex calculations some way but I have encountered a situation & wondering if there is any way to do this in excel or using some VBA. I would be grateful to all of you for this.

My excel sheet is some thing like this

Order No Require Item Require Qty Stock Status
10001 XXXXX 100 300 200
10001 YYYYY 100 200 100
10002 XXXXX 200 300 200
10002 YYYYY 200 200 100
10003 XXXXX 100 300 100
10003 YYYYY 100 100 000

There are a lot of different orders for different different products but all have some common ingredients. On any given day If I see my pending orders & Inventory then it looks like above & showing all stock available but I can't process all my orders as stock will be reduced after first order, then I wont be able to process second order but I can process 3rd one . What I want to do is process this data some way that It takes the first order & if all the material is available then reduce the stock so in above example after first order XXXXX will be left 200 & YYYYY 100. Then for next order XXXXX is sufficient but YYYYY is not available so no stock shall be deducted & for third order calculation also XXXXX will be 200 & YYYYY will be 100 & if there is any next order then for that XXXXX = 100 & YYYYY = 0 . Then show it in next column.

I don't think it's possible without VBA but I am not much familiar with that other than copy & pasting the codes from web & modifying here & there. But an expert might do it with excel also.. (I will appriciate if any1 can give me a VBA code ) This is just example in actual my data is 20000 or more row with a lot of product combinations.

Thanking you all in advance for your input & support

Hey all, I'm new to these forums and slightly new to Excel.

Is it possible to do this? I have a spreadsheet application with a large number of complex calculations that I want users to be able to access on their blackberrys/laptops/desktops online. I've considered simply re-creating the spreadsheet as an asp and using javascript as a backend to perform the calculations, but due to time constraints it wouldn't be very viable option for me to recreate this from scratch. The spreadsheet is so large it would likely take me a few days just to re-create the design in ASP!

Any thoughts, recommendations? I tried saving the spreadsheet as an mhtml and considered just replicating functionality from that with javascript, but that produced some unmaintainable markup, and I don't even know where to begin to get what used to be controls working (though I wasn't expecting too much...).

In a nuthsell, I have a spreadsheet, and I want to make it LIVE.

P.S.

Oops on the title typo.

Hi, I am making an excel program that performs a series of complex calculations then produces several charts of the output. I have the output in a table of a known lenght and width. The problem comes when attempting to create the graphs, I first delete all the old graphs and make new ones. Then I try to add the series to the graphs. I want the graph to be one column vs another column but I have only been able to get it to graph a column vs 1, 2, 3, ect.

I need to know how to set the x-axis source data for a given series.

thanks for any help in advance

Hi,

I feel a bit silly asking this in the presence of so many obvious excel gurus: but here it goes.

I've created some user forms, activex controls, etc. that do a series of complex calculations, supported by VBA and it's at the point where I want to roll it out.

How do I hide / get rid of the active cell marker on the sheets? I've looked in the "options - view" section and have lucked out. Can anyone help?

Cheers,

Jason


No luck finding an answer? You could always try Google.