Excel Trunc

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)

Thanks,
Microsoft Excel

Last Comment
W.E.B
Shaun Kline

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

why to complicate it with this formula.

you can simply use =TRUNC(A1,2)
W.E.B

The formula is for pricing. (PRICE PLANS)
I wish it was that easy to just use =TRUNC(A1,2)
Professor J

you can then use it iniside text function to hide other zeors
like this
=TEXT(TRUNC(A1,2),"\$#,##0.00")
W.E.B

I don't want to Round.
I just need to display the 2 decimals. (Without Rounding)
Professor J

Wass can you upload a sample dummy file?
Rgonzo1971

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
W.E.B

Thank you guys,
Rgonzo1971, I'm testing your formula now,

Attached is a sample file.
Montoya

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

No Sample
W.E.B

Attached Sample.
Sample.xlsx
Rgonzo1971

As I said what is the result you  want in b9 for example
EE20160115_1Sample.xlsx
Professor J

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")
Rgonzo1971

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

Thank you Guys,
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