SIPOC Diagram: Process Mapping

References In Excel

In formulas references to cells or locations are excel are used. 

What kind of references are there in excel?

Referencing in the same sheet:

Relative: Ex. A1, B1 in the formula =A1*B1 written in the cell C1 is a relative reference. If you will copy paste this formula from cell C1 to C2 the formula will be pasted as =A2*B2 i.e. relative to the positioning of C2, excel is smart enough to find the relative quantities that have to be multiplied. 

Absolute: Ex. $A$1, $B$1 etc is absolute positioning. Why would you absolute positioning? One of the many possible cases is when the formula uses some constant say pi(=22/7). If you have put the value of constant at position say, A1 then you can refer to this constant in all the formulas by $A$1. When you copy paste formula from a cell in this case the $A$1 reference remains unchanged.

Row Absolute: Ex. A$1, B$1 etc i.e. only column will change, row is fixed as 1.

Column Absolute: Ex. $A1, $B1 etc i.e. only row will change, column is fixed as 1.

Referencing to a cell in other worksheet:

The general way is by using 'sheetname'!cell.

Ex: 'Sheet1'!A2

Referencing to cell in other workbook:

'[workbookname.xls]sheetname'!cell

Ex: '[Expenditure.xls]Household'!B3

If the workbook we are referencing to is closed then we have to give its path too as:

'D:\ExcelPractice\[workbookname.xls]sheetname'!cell

Thats it about referencing for now.

PS: Corrections/Suggestions/Comments are welcome!

Excel: Random Stuff Part 1

To write same thing on multiple cells

1. Select a range of cells say from A1 to A10 

2. Write whatever you want to write

3. Press Ctrl+Enter

If what you want to write is already there in a cell and you want to copy it in cells adjacent to it 

1. Select the cells where you want the text

2. Select the cell which has the required text

3. Press F2

4. Press Ctrl+Enter

To copy a cell contents on the cell to the right, 

1. Select the range of cells 

2. Press Ctrl+R

To copy a cell contents on the adjacent cells below, 

1. Select the range of cells 

2. Press Ctrl+D

To write a fraction 

1. Write 0 followed by a space and the fraction with the slash

2. Press Enter

Use TAB or Enter to move in a selected range of cells

To edit contents of cell Press F2 and it takes you in the cell

To convert a formula into its numerical value 

1. Go in the cell 

2. Press F2

3. Select the formula or part of the formula which you want converted

4. Press F9

5. Press Enter

This method loses the formula while retaining the calculated value.

To compare values in two cells

1. Use expression like =A1=B1, =A1>B1, =A1<B1

2. It returns either True or False

Table of Operators in Excel

And and Or in Excel

=OR(A1=100, A1=200) returns TRUE if either of A1=100 or A1=200 holds else FALSE

=AND(A1<100, A2>200) returns TRUE if both A1<100 and A2>200 holds else FALSE

Operator Precedence in Excel

Calculating Formulas

If calculation mode (Formulas>Calculation>Calculation Options) is set manual then:

Press F9 to recalculate the sheet

scanf() Infinite Loop

Every call to scanf picks up from where the last one stopped matching the input.  This means that if an error occurred with the previous scanf, the input it failed to match is still left unread, as if the user typed ahead.  If care isn't taken to discard error input, and a loop is used to read the input, your program can get caught in an infinite loop.

(Source: http://wpollock.com/CPlus/PrintfRef.htm)

You can also use flushall(); within the loop and before calling the scanf to tackle this problem.