Ms Office Tune Up

One common worksheet task is to calculate a list price for a product based on the result of a formula that factors in production costs and profit margin. If the product is sold at retail, you likely need the decimal (cents) portion of the price to be .95 or .99 or some other stan­dard value. You can use the INT() function to help with this “rounding,” as shown in the following:
INT(number)

number The number you want to round.

For example, INT(6.75) returns 6. (Note, too, that for negative values, INT() returns the next number away from 0. For example, INT(-3.42) returns -4)._

To calculate a price point, the simplest case is to always round up the decimal part to .95. Here’s a formula that does this:

=INT(RawPrice) + 0.95

Assuming that RawPrice is the result of the formula that factors in costs and profit margin, the formula simply adds 0.95 to the integer portion. (Note, too, that if the decimal portion of RawPrice is greater than .95, the formula rounds down to .95.)

Another case is to round up to .50 for decimal portions less than or equal to 0.5 and to round up to .95 for decimal portions greater than 0.5. Here’s a formula that handles this scenario:

=VALUE(INT(RawPrice) & IF(RawPrice - INT(RawPrice) <= 0.5, “.50″, “.95″))

Again, the integer portion is stripped from the RawPrice. Also, the IF() function checks to see if the decimal portion is less than or equal to 0.5. If so, the string .50 is returned; other­wise, the string .95 is returned. This result is concatenated to the integer portion, and the VALUE() function ensures that a numeric result is returned, as shown in Figure 1.

Figure 1A worksheet that uses INT() and IF()to calcu­late the retail price point of several books given the Raw Price calculated from the costs and desired margin.

Technorati Tags: product price point, excel 2007

Popularity: 2% [?]