Round to nearest 0.50

The Mround function will do what you want.

=Mround(1.25, 0.5) returns 1.5





MROUND

Returns a number rounded to the desired multiple.


Syntax

MROUND(number,multiple)

Number   is the value to round.

Multiple   is the multiple to which you want to round number.


Remarks

MROUND rounds up, away from zero, if the remainder of dividing number
by multiple is greater than or equal to half the value of multiple.

Examples

MROUND(10, 3) equals 9

MROUND(-10, -3) equals -9

MROUND(1.3, 0.2) equals 1.4

MROUND(5, -2) equals #NUM!

Sum from column number

OFFSET formula gives us reference to a range, from a given starting point with given number of rows to be considered and number of columns to be considered.

OFFSET formula looks like this:

=OFFSET (starting point, rows to move for starting point, columns to move for starting point, number of rows to be considered, number of columns to be considered)

To get total of number of row and/or number of columns use the formula

= Sum (Offset (SP, RSP, CSP, NOF, NOC))

Tip- In case dealing with years please -1 from the year variable to get exact results.

Trace a number in a range

Lookup a number in a range.
 Trace a number in a number in a table range.

An array formula can help. (Raise your hand if you don't know how to enter an array formula.)

The formula used
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1)-1,0)}
can also be written as:
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1))}

If min and max numbers of all number range always have 2 digits then the formula can be shorten by replacing FIND(" ",$B$2:$B$10)-1 with number 2:
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1)-1,0)}
or
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1))}
 

Attached Images

  

 Courtesy: ANHN

Location Australia

http://www.vbforums.com/showthread.php?534120-Excel-Formula-Number-within-range..-Resolved-and-then-some

 

To display Brackets for negative figure in Excel

Right Click on desired Cell >Format Cells >Number and select Custom and enter as below:-

#,##0;(#,##0);0

In case result is required in Decimals enter .00 after the zeros above.

Create two Y Axis

First plot the data on existing y axis

The result may seem absurd for the moment

Then

Select the line> Right Click> Format Data Series> Series Options > Plot Series On >>> Select Secondary Axis

And Vella !!!!


Now please don't ask the logic behind it.

http://www.techonthenet.com/excel/charts/2_y_axes.php 

Delete Blank Rows in Table Excel

1. Select the cells in one column from the top of your list to the bottom.
2. Make sure that all the blank cells in this selected range are the rows you want to delete.
3. Press the F5 key on your keyboard (or select Edit, Goto).
4. Click the Special button.
5. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
6. Now choose Edit, Delete, select the Entire Row option and click OK.

To round off to nearest Multiple

Floor Rounding off to nearest multiple.

Number Rounded Down
1.5 1 =FLOOR(C4,1)
2.3 2 =FLOOR(C5,1)
2.9 2 =FLOOR(C6,1)
123 100 =FLOOR(C7,50)
145 100 =FLOOR(C8,50)
175 150 =FLOOR(C9,50)

What Does It Do ?
This function rounds a value down to the nearest multiple specified by the user.

Syntax
=FLOOR(NumberToRound,Significant Value)

To get Excel figures in Indian Rs.

Right click a cell>Select Format Cells>In Number Tab>Select Custom Option>In the box under the “Type” word paste the following codes as per need




If result is desired in crores: - ##\,##\,##\,##0.00



If Results is desired in Lakhs: - ##\,##\,##0.00;##,##0.00





But remember this trick will work only in case you have positive numbers in cell i.e if anything is below 0 in a cell this trick won’t work on same.



For the next time these two formulas will get stored as last available option in custom menu.



Disadvantage of this trick.



Since the formats of cells have been changed to custom it may not give effect to results in case addition/subtraction etc is done with a cell of a different format. But I have checked its compatibility with quite a few formats and it works just fine.