W.E.B

asked on

Hello,

Not sure what I'm doing wrong, but the Trunc is not giving the proper results. (I need to display 2 Decimals)

This is my Formula

=((ROUND((U9+(U9*$P$1%))*20,0)/20)) - ((ROUND((U9+(U9*$P$1%))*20,0)/20))*$P$2%

Example

$4.465 Need it to be $4.46

$7.116 Need it to be $7.11

$13.3655 Need it to be $13.36

I tried (With errors)

=TRUNC(((ROUND((U9+(U9*$P$1%))*20,0)/20)) - ((ROUND((U9+(U9*$P$1%))*20,0)/20))*$P$2%,2)

Your help is appreciated.

Thanks,

Not sure what I'm doing wrong, but the Trunc is not giving the proper results. (I need to display 2 Decimals)

This is my Formula

=((ROUND((U9+(U9*$P$1%))*2

Example

$4.465 Need it to be $4.46

$7.116 Need it to be $7.11

$13.3655 Need it to be $13.36

I tried (With errors)

=TRUNC(((ROUND((U9+(U9*$P$

Your help is appreciated.

Thanks,

Last Comment

If you always want to round down to two decimal places, try using the ROUNDDOWN function instead of TRUNC.

why to complicate it with this formula.

you can simply use =TRUNC(A1,2)

you can simply use =TRUNC(A1,2)

ASKER

The formula is for pricing. (PRICE PLANS)

I wish it was that easy to just use =TRUNC(A1,2)

I wish it was that easy to just use =TRUNC(A1,2)

you can then use it iniside text function to hide other zeors

like this

=TEXT(TRUNC(A1,2),"$#,##0.00")

like this

=TEXT(TRUNC(A1,2),"$#,##0.

ASKER

I don't want to Round.

I just need to display the 2 decimals. (Without Rounding)

I just need to display the 2 decimals. (Without Rounding)

Wass can you upload a sample dummy file?

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

Thank you guys,

Rgonzo1971, I'm testing your formula now,

Attached is a sample file.

Rgonzo1971, I'm testing your formula now,

Attached is a sample file.

just out of sheer curiosity, why isn't the column formatted for two decimals?

No Sample

ASKER

Attached Sample.

Sample.xlsx

As I said what is the result you want in b9 for example

EE20160115_1Sample.xlsx

use this formula lets say in B9

=TEXT(TRUNC(((ROUND((U9+(U9*$P$1%))*20,0)/20)) - ((ROUND((U9+(U9*$P$1%))*20,0)/20))*$P$2%,2),"$#,##0.00")

=TEXT(TRUNC(((ROUND((U9+(U

Or go on Home / Number / and click the .00->.0 to reduce the number of decimals after the trunc

ASKER

Thank you Guys,

Rgonzo1971 Formula worked, thank you.

Rgonzo1971 Formula worked, thank you.

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY