Code Formula

This function shows the ANSI value of a single character, or the first character in a piece of text.The ANSI character set is used by Windows to identify each keyboard character by usinga unique number.There are 255 characters in the ANSI set.

Click on image to enlarge....

Clean Formula

This function removes any nonprintable characters from text.These nonprinting characters are often found in data which has been imported from other systems such as database imports from mainframes.

Click on image to enlarge.....

	Dirty Text	Clean Text
	Hello	Hello	=CLEAN(C4)
	Hello	Hello	=CLEAN(C5)
	Hello	Hello	=CLEAN(C6)
What Does It Do?
This function removes any nonprintable characters from text.
These nonprinting characters are often found in data which has been imported
from other systems such as database imports from mainframes.
Syntax
=CLEAN(TextToBeCleaned)
Formatting
No special formatting is needed. The result will show as normal text.

Choose Formula

This function picks from a list of options based upon an Index value given to by the user.

Click on image to enlarge.....

	Index Value	Result
	1	Alan	=CHOOSE(C4,"Alan","Bob","Carol")
	3	Carol	=CHOOSE(C5,"Alan","Bob","Carol")
	2	Bob	=CHOOSE(C6,"Alan","Bob","Carol")
	3	18%	=CHOOSE(C7,10%,15%,18%)
	1	10%	=CHOOSE(C8,10%,15%,18%)
	2	15%	=CHOOSE(C9,10%,15%,18%)
What Does It Do?
This function picks from a list of options based upon an Index value given to by the user.
Syntax
=CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)
Formatting
No special formatting is required.
Example
The following table was used to calculate the medals for athletes taking part in a race.
The Time for each athlete is entered.
The =RANK() function calculates the finishing position of each athlete.
The =CHOOSE() then allocates the correct medal.
The =IF() has been used to filter out any positions above 3, as this would cause
the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.
Name	Time	Position	Medal
Alan	1:30	2	Silver	=IF(D30<=3,CHOOSE(D30,"Gold","Silver","Bronze"),"unplaced")
Bob	1:15	4	unplaced	=IF(D31<=3,CHOOSE(D31,"Gold","Silver","Bronze"),"unplaced")
Carol	2:45	1	Gold	=IF(D32<=3,CHOOSE(D32,"Gold","Silver","Bronze"),"unplaced")
David	1:05	5	unplaced	=IF(D33<=3,CHOOSE(D33,"Gold","Silver","Bronze"),"unplaced")
Eric	1:20	3	Bronze	=IF(D34<=3,CHOOSE(D34,"Gold","Silver","Bronze"),"unplaced")
		=RANK(C34,C30:C34)

Char Formula

This function converts a normal number to the character it represent in the ANSI character set used by Windows.

Click on image to enlarge....

Cell Formula............

This function examines a cell and displays information about the contents, position and formatting.

Click on image to enlarge......

Ceiling Formula

This function rounds a number up to the nearest multiple specified by the user.

Click on images to enlarge...

	Number	Raised Up
	2.1	3	=CEILING(C4,1)
	1.5	2	=CEILING(C5,1)
	1.9	2	=CEILING(C6,1)
	20	30	=CEILING(C7,30)
	25	30	=CEILING(C8,30)
	40	60	=CEILING(C9,30)
What Does It Do ?
This function rounds a number up to the nearest multiple specified by the user.
Syntax
=CEILING(ValueToRound,MultipleToRoundUpTo)
The ValueToRound can be a cell address or a calculation.
Formatting
No special formatting is needed.
Example 1
The following table was used by a estate agent renting holiday apartments.
The properties being rented are only available on a weekly basis.
When the customer supplies the number of days required in the property the =CEILING()
function rounds it up by a multiple of 7 to calculate the number of full weeks to be billed.
		Days Required	Days To Be Billed
	Customer 1	3	7	=CEILING(D28,7)
	Customer 2	4	7	=CEILING(D29,7)
	Customer 3	10	14	=CEILING(D30,7)
Example 2
The following table was used by a builders merchant delivering products to a construction site.
The merchant needs to hire trucks to move each product.
Each product needs a particular type of truck of a fixed capacity.
Table 1 calculates the number of trucks required by dividing the Units To Be Moved by
the Capacity of the truck.
This results of the division are not whole numbers, and the builder cannot hire just part
of a truck.
	Table 1
	Item	Units To Be Moved	Truck Capacity	Trucks Needed
	Bricks	1000	300	3.33	=D45/E45
	Wood	5000	600	8.33	=D46/E46
	Cement	2000	350	5.71	=D47/E47
Table 2 shows how the =CEILING() function has been used to round up the result of
the division to a whole number, and thus given the exact amount of trucks needed.
	Table 2
	Item	Units To Be Moved	Truck Capacity	Trucks Needed
	Bricks	1000	300	4	=CEILING(D54/E54,1)
	Wood	5000	600	9	=CEILING(D55/E55,1)
	Cement	2000	350	6	=CEILING(D56/E56,1)
Example 3
The following tables were used by a shopkeeper to calculate the selling price of an item.
The shopkeeper buys products by the box.
The cost of the item is calculated by dividing the Box Cost by the Box Quantity.
The shopkeeper always wants the price to end in 99 pence.
Table 1 shows how just a normal division results in varying Item Costs.
Table 1
Item	Box Qnty	Box Cost	Cost Per Item
Plugs	11	£20	1.81818	=D69/C69
Sockets	7	£18.25	2.60714	=D70/C70
Junctions	5	£28.10	5.62000	=D71/C71
Adapters	16	£28	1.75000	=D72/C72
Table 2 shows how the =CEILING() function has been used to raise the Item Cost to
always end in 99 pence.
Table 2
Item	In Box	Box Cost	Cost Per Item	Raised Cost
Plugs	11	£20	1.81818	1.99
Sockets	7	£18.25	2.60714	2.99
Junctions	5	£28.10	5.62000	5.99
Adapters	16	£28	1.75000	1.99
				=INT(E83)+CEILING(MOD(E83,1),0.99)
Explanation
=INT(E83)			Calculates the integer part of the price.
=MOD(E83,1)			Calculates the decimal part of the price.
=CEILING(MOD(E83),0.99)			Raises the decimal to 0.99