Rounddown to 1 decimal place if <20

[IanG]

I need a formula that rounddowns prices to 1 decimal place (e.g. 3.96 = 3.9) if the number is less than 20.

For example,
$4.17 = $4.10
$2.96 = $2.9
$21.22 = $21.22

Also, the sheet has 5000 lines and prices that range from $2 to $10,000

 

[AlanSidman]

=if(A1<20,rounddown(A1,1),A1) and copy down. Assumes your data in column A, put this in the column you wish the results to be in.

 

[IanG]

=if(A1<20,rounddown(A1,1),A1) and copy down. Assumes your data in column A, put this in the column you wish the results to be in.

Awesome thats exactly what I needed.
Thanks

 

[IanG]

How do I change the formula to rounddown only a specific section (e.g $20-$50)

 

[rahulshewale1]

hii @IanG

try this formula
=IF(AND(G12>=20,G12<=50),ROUNDDOWN(G12,1),G12)


Regard
Rahul shewale

 

[p45cal]

I can see where this is going. You're going to have different price ranges that you want rounding differently. You can set up a table thus:

then use it like this:
=ROUNDDOWN(G3,INDEX($L$2:$L$9,MATCH(G3,$K$2:$K$9)))
where G3 contains your original unrounded price. Copy it down the whole price list.
It's up to you to tweak it, I'm just showing the principle.
See attached.