Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on 

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)

Your help is appreciated.
Thanks,
Microsoft Excel
Avatar of undefined
Last Comment
W.E.B
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image
If you always want to round down to two decimal places, try using the ROUNDDOWN function instead of TRUNC.
Avatar of Professor J
Professor J
why to complicate it with this formula.

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

ASKER

The formula is for pricing. (PRICE PLANS)
I wish it was that easy to just use =TRUNC(A1,2)
Avatar of Professor J
Professor J
you can then use it iniside text function to hide other zeors
like this
=TEXT(TRUNC(A1,2),"$#,##0.00")
Avatar of W.E.B
W.E.B

ASKER

I don't want to Round.
I just need to display the 2 decimals. (Without Rounding)
Avatar of Professor J
Professor J
Wass can you upload a sample dummy file?
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of W.E.B
W.E.B

ASKER

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

Attached is a sample file.
Avatar of Montoya
Montoya
just out of sheer curiosity, why isn't the column formatted for two decimals?
Avatar of Rgonzo1971
Rgonzo1971
No Sample
Avatar of W.E.B
W.E.B

ASKER

Attached Sample.
Sample.xlsx
Avatar of Rgonzo1971
Rgonzo1971
As I said what is the result you  want in b9 for example
EE20160115_1Sample.xlsx
Avatar of Professor J
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")
Avatar of Rgonzo1971
Rgonzo1971
Or go on Home / Number / and click the .00->.0 to reduce the number of decimals after the trunc
Avatar of W.E.B
W.E.B

ASKER

Thank you Guys,
Rgonzo1971 Formula worked, thank you.
Microsoft Excel
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
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent