price rounding

[duhar]

As a price is calculated, it references a table and is rounded to the top of the range it falls within. $8.13 falls within 7.99 - 8.49 resulting in 8.49.

Thanks

[ This Message was edited by: duhar on 2002-05-19 18:08 ]

[Anne Troy]

Use vlookup.

Your lookup table should contain only the top values (not bottom and not range) of each increment in the first column and the price to apply in the second column.

Download example Vlookup from MyVlookup at:

http://www.thewordexpert.com/downloads.htm

[Asala42]

Hey,

As a price is calculated, it references a table and is rounded to the top of the range it falls within. $8.13 falls within 7.99 - 8.49 resulting in 8.49.

Assuming your value of $8.13 is in the cell A2, Have you tried something like:

=CEILING(A2,0.5)-0.01

Adam

[Aladin Akyurek]

On 2002-05-19 18:06, duhar wrote:
As a price is calculated, it references a table and is rounded to the top of the range it falls within. $8.13 falls within 7.99 - 8.49 resulting in 8.49.

Thanks

[ This Message was edited by: duhar on 2002-05-19 18:08 ]

I wished you had mentioned the table range!

Lets say that the price range in your lookup table is C2:C25 and A1 houses a price that you want to lookup up in order to "round".

=INDEX(C2:25,MATCH(A1,C2:25)+1)

This formula will give you the "top" value for every price you look up.

=IF(COUNTIF(C2:25,A1),A1,INDEX(C2:25,MATCH(A1,C2:25)+1))

This formula determines whether the price you look up exists in the table (exact match). If so, it returns the price itself, otherwise it sets the price to the "top" value it finds.

Aladin