Change the summary calculation of multiple pivot table fields at once using Macro (Excel)

Pivot table is useful to summary calculation for table. However, there isn’t a simple way to adjust multiple fields to the same function. The traditional way, you can right-click on the pivot table and adjust one-by-one.

Fortunately, there is the easy way to change multiple pivot-fields to the same function, by using the macro-excel. The full reference can be obtained from Moxie (2013). Here is a short macro that converts all the fields in a selected pivot table to the Sum function (code as xlSum) as follow:

Figure 1: Macro code for adjust multiple fields to the same function (reproduced from Moxie(2013)) (Note: Make sure you have selected the pivot table to change before playing the macro)
Public Sub PivotFieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie
 
Dim pf As PivotField
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlSum
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub

You can change xlSum with other function such as:  xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP. The detail of each function is illustrated in Table 1.

Table  1: Detail of XlConsolidationFunction enumeration (MSDM Microsoft, 2014)
Member name Description
xlAverage Average.
xlCount Count.
xlCountNums Count numerical values only.
xlMax Maximum.
xlMin Minimum.
xlProduct Multiply.
xlStDev Standard deviation, based on a sample.
xlStDevP Standard deviation, based on the whole population.
xlSum Sum.
xlVar Variation, based on a sample.
xlVarP Variation, based on the whole population.
xlUnknown No subtotal function specified.
xlDistinctCount Count using Distinct Count analysis.

References:

  1. Moxie, D. (2013). How to change multiple pivot table fields to Sum Function – Excel Pivots. [online] Excel Pivots. Available at: http://excelpivots.com/excel/change-multiple-pivot-table-fields-sum-function/ [Accessed 30 Nov. 2014].
  2. MSDM Microsoft (2014). XlConsolidationFunction enumeration (Microsoft.Office.Interop.Excel). [online] Available at: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlconsolidationfunction.aspx [Accessed 30 Nov. 2014].
Posted in Code | Tagged , , | Leave a comment

Resampling Data

Posted in Mathematics | Leave a comment

Read/edit the built-in Matlab functions

I found a good tip from the matlab community to find out the built-in function in Matlab. You can edit and change it. The tip as follows:

% You can use the [which] command to find the path name to the

% built-in function in question, i.e. -

which fft

% then point Matlab to that folder on your computer so that

% the fft.m file is in your "current folder" window. Then use

% the [edit] command to edit the function, i.e. -

edit fft

% The code for the fft function will appear in the editor window.

% Often it is useful to make small changes to the built-in code

% then comment out the changes as soon as you're done so that

% Matlab will continue to function as designed.
Posted in Code | Tagged , , | Leave a comment

Design, safety and management of the reservoir

I found very good online article on the dam management on the view of hydrology and hydraulic. The article was achieved from Dr. Sobri’s academic website (Link: http://civil.utm.my/sobriharun/drama/). Very interesting. The article as follows:

A Dam Management team requires knowledge of civil engineering (structural, hydraulics, hydrology, geology and geotechnical), electrical and mechanical engineering. The main objective of dam safety management is to ensure that the dams able to sustain on two aspects; strength and operation. The standard operation procedures (SOP) determine the successful of dam safety management.The strength normally related to structural, geology and geotechnical assessments. The knowledge in hydraulics, hydrology, electrical and mechanical engineering are required in the reservoir operation.Construction of a large dam ( > 15 meter high) or a major large dam ( > 30 meter) would impose the risk to nearby inhabitants. Dam risk management program would help the operator and manager on minimising the risk and maximizing the safety.

To ensure the safety of the inhabitants along downstream of dam during the heavy rainfall, the flow modelling through spillway adopting minimum of 100 years design (ARI or return period) rainfall shall be performed by the dam managers. Several critical storm patterns could be inspected during the simulation of outflow hydrograph and potential flood inundation map along the downstream river.

Fig. 1: Reservoir system

Sediments flow into reservoir system due to erosion from upstream catchment could reduce the reservoir storage capacity in long-term. The erosion control at upstream catchment is less costly compared to removal of sediments in the reservoir. The nature of inflow pattern into the reservoir system also changes with time and this variation would affect the spillway capacity of reservoir system. The computed probable maximum flood (PMF) value shall be below the designed value to indicate that the dam is safely design. It is the PMF condition with adequate capacity of the spillway to discharge the PMF to the downstream in the event of extreme rainfall. PMF provides an upper limit of the interval within the engineer must operate and design.

The spillway capacity of a dam is regularly inspected for at least 10,000 years return period of PMF. The PMF is derived from probable maximum precipitation (PMP) using rainfall-runoff model. For several stages of spillway operation (gradual releases) of a major large dam, the capacity could be inspected from 10-yr, 100-yr, 1000-yr, 10,000-yr and 100,000-yr. If the spillway capacity below the design level, the possibility of upgrading the system should be adopted by dam manager. Gradual water release has advantages that would alter the shape of rising limb for the outflow hydrograph through spillway due to extremely huge flood magnitude.

Dam failure is possible due to lack of management on two attributes; the structural strength and optimal operation. Most of the dams in the world failed due to the overtopping and piping. In general, the overtopping failure occurs after unexpected extremely heavy rainfall and the piping failure occurs due to unsatisfactory maintenance of seepage in the earth dam.

The dam break modelling also necessary to reveal illustration of potential flood inundation due to the greatest possible heavy rainfall (probable maximum precipitation, PMP). Normally, the hydrologists adopt within 10,000 years to 100,000 years design rainfall that equivalent to probability of occurrence by 0.01 percent and 0.001 percent respectively. The results of dam break modelling are the timing, depth and extent of flood the inundation along downstream that would likely possibly happen due to dam failure.

The international organizations related to dam building and safety management are the International Commission on Large Dams (ICOLD) and the World Commission on Dams (WCD).

Posted in Hydrology | Tagged , , , | Leave a comment

Top 20 PhD Viva Questions

Fig. 1: Summary of PhD. process

I would like to share some good general questions for PhD viva. Useful for PhD candidates and also for examiners. Credit to my main supervisor who forward this information.

  1. What is original or novel about your work/thesis?
  2. What are the main issues / debates in your subject area? What is the current state of the art in your field?
  3. What were the crucial research decisions that you made? How did they impact what you did?
  4. How have you analysed/evaluated your work?
  5. Why have you tackled the problem in this way? What alternatives did you consider?
  6. What do your results mean? Are there longer term implications for industry?
  7. How could you improve your work/thesis?
  8. What had not been done on this topic/field before?
  9. What theories or models or analytical techniques did you use?
  10. Are the techniques you have used appropriate for this topic?
  11. What are the theoretical basis/underpinnings to your work?
  12. How long do you expect your work to remain innovative? How do you expect the field to develop in the next 5-10 years?
  13. What sets your work apart from others? Who are the leading researchers in your field?
  14. What issues arose during this work? How did you resolve any issues which arose in the course of your research?
  15. What have you learned from the process of doing a PhD? How will you apply what you have learnt in your future career?
  16. How did you deal with the practical/fieldwork aspect of your study?
  17. Do you think that your recommendations are appropriate or widely applicable?
  18. Is there scope for further study on this topic? What would you recommend the next student on this topic to do?
  19. Do your contributions have a limited timescale? When do you think your work will be obsolete?
  20. What have you done that merits the award of a PhD?
Posted in Tips | Tagged , , | Leave a comment