SOLVED
Home

Decimal Places Difficulty

%3CLINGO-SUB%20id%3D%22lingo-sub-926616%22%20slang%3D%22en-US%22%3EDecimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-926616%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20can%20be%20seen%20in%20the%20attachment%2C%20I%20have%20the%20display%20set%20to%20currency%20and%20to%20two%20decimal%20places%20but%20the%20values%20show%20as%20.00%20instead%2C%20such%20as%2010%2F3%20should%20read%203.33%20but%20displays%203.00.%20So%2C%20how%20can%20I%20get%20the%20two%20decimal%20places%20to%20show%20the%20actual%20values%20rather%20than%20zeros.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ER%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-926616%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-926756%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-926756%22%20slang%3D%22en-US%22%3E%3CP%3EQUOTIENT%20returns%20the%20%3CSTRONG%3Einteger%20portion%3C%2FSTRONG%3E%20of%20a%20division.%20The%20decimal%20portion%20will%20never%20be%20included%20in%20the%20returned%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934708%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934708%22%20slang%3D%22en-US%22%3EOK%2C%20thanks.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20just%20a%20basic%20user%20of%20Excel%20and%20used%20quotient%20because%20it's%20the%20first%20one%20I%20came%20across%20that%20would%20divide%20one%20number%20into%20another.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20is%20there%20a%20function%20I%20can%20use%20in%20currency%20that%20will%20include%20the%20decimal%20portion%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3CBR%20%2F%3E%3CBR%20%2F%3ER%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934749%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934749%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20per%20the%20screenshot%20you%20would%20type%3CBR%20%2F%3E%3CBR%20%2F%3E%3DF55%2FG55%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-935543%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-935543%22%20slang%3D%22en-US%22%3EThe%20exchange%20between%20you%20and%20Wyn%20Hopkins%20points%20up%20a%20real%20irony%20here.%20You%20describe%20yourself%20as%20a%20basic%20user%20of%20Excel%2C%20yet%20you%20were%20assuming%20that%20there%20had%20to%20be%20a%20%22fancy%22%20solution%20to%20the%20division%20of%20one%20number%20by%20another.%20In%20fact%2C%20at%20the%20most%20basic%20level%20you've%20got%20all%20of%20the%20basic%20arithmetic%20functions%20we%20learned%20in%20primary%20school%3A%3CBR%20%2F%3EADDITION%20by%20%3DA1%2BB1%3CBR%20%2F%3ESUBTRACTION%20by%20%3DA1-B1%3CBR%20%2F%3EMULTIPLICATION%20by%20%3DA1*B1%20(granted%2C%20in%20primary%20school%20we%20used%20%22x%22)%3CBR%20%2F%3EDIVISION%20by%20%3DA1%2FB1%3CBR%20%2F%3E%3CBR%20%2F%3ENow%2C%20because%20it's%20very%20common%20to%20add%20a%20whole%20series%20of%20numbers%2C%20usually%20in%20a%20column%2C%20they've%20included%20a%20fairly%20basic%20function%20called%20SUM%2C%20as%20in%20%3DSUM(A1%3AZ1)%3CBR%20%2F%3E%3CBR%20%2F%3EAnyway%2C%20I'm%20writing%20just%20to%20say%2C%20Excel%20has%20a%20lot%20of%20power%2C%20a%20LOT%2C%20but%20as%20you've%20discovered%2C%20it%20can%20backfire%20if%20you%20don't%20take%20the%20time%20to%20actually%20read%20a%20little%20bit.%20When%20you%20get%20a%20result%20that%20doesn't%20make%20sense--as%20you%20were--go%20back%20and%20look%20at%20the%20description%20(use%20the%20%22Help%22%20menu%20and%20search%20for%20the%20name%20of%20the%20function%20used)....%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20make%20you%20feel%20better%20here%2C%20I've%20been%20using%20Excel%20since%20(in%20a%20sense)%20before%20it%20existed.%20I%20began%20programming%20on%20mainframes%20in%20the%20early%20'70s%2C%20moved%20quickly%20to%20PCs%20when%20they%20became%20available%2C%20used%20in%20succession%2C%20Lotus%201-2-3%2C%20Quattro%20(few%20remember%20it)%2C%20and%20then%20Excel%20in%20all%20its%20generations.%20BUT%2C%20here's%20where%20you%20can%20find%20some%20relief%2C%20until%20your%20question%20was%20posted%20here%2C%20I'd%20never%20heard%20of%20the%20QUOTIENT%20function%20(it's%20relatively%20new).%20So%20thank%20you%20for%20adding%20to%20my%20knowledge%20of%20Excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-951761%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-951761%22%20slang%3D%22en-US%22%3EThat's%20done%20the%20trick.%20Thanks!%3CBR%20%2F%3E%3CBR%20%2F%3ER%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-952213%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Places%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-952213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Relatively%20new'%20is%2C%20by%20definition%2C%20a%20relative%20term!%26nbsp%3B%20I%20found%20a%20list%20of%20functions%20that%20indicates%20that%20QUOTIENT%20existed%20and%20was%20not%20newly%20introduced%20in%202010.%26nbsp%3B%20Like%20you%2C%20I%20only%20came%20across%20the%20function%20much%20later%20and%20it%20took%20a%20while%20to%20realise%20that%20it%20performs%20an%20integer%20divide.%26nbsp%3B%20Now%20it%20is%20a%20regular%20part%20of%20my%20repertoire%20used%20to%20unpivot%20crosstab%20tables%20and%20arrays.%26nbsp%3B%20Used%20with%20a%20base%201%20index%20k%2C%20the%20formulas%3C%2FP%3E%3CP%3E%3D%201%20%2B%20QUOTIENT(%20k-1%2C%20n%20)%3C%2FP%3E%3CP%3E%3D%201%20%2B%20MOD(%20k-1%2C%20n%20)%3C%2FP%3E%3CP%3Eprovide%20row%20and%20column%20indices%20for%20the%202D%20lookup.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Reboot
Occasional Contributor

Hi

 

As can be seen in the attachment, I have the display set to currency and to two decimal places but the values show as .00 instead, such as 10/3 should read 3.33 but displays 3.00. So, how can I get the two decimal places to show the actual values rather than zeros.

 

Thanks.

 

R

6 Replies

QUOTIENT returns the integer portion of a division. The decimal portion will never be included in the returned value.

OK, thanks.

I am just a basic user of Excel and used quotient because it's the first one I came across that would divide one number into another.

So, is there a function I can use in currency that will include the decimal portion?

Thanks.

R
Solution
Hi

So per the screenshot you would type

=F55/G55
The exchange between you and Wyn Hopkins points up a real irony here. You describe yourself as a basic user of Excel, yet you were assuming that there had to be a "fancy" solution to the division of one number by another. In fact, at the most basic level you've got all of the basic arithmetic functions we learned in primary school:
ADDITION by =A1+B1
SUBTRACTION by =A1-B1
MULTIPLICATION by =A1*B1 (granted, in primary school we used "x")
DIVISION by =A1/B1

Now, because it's very common to add a whole series of numbers, usually in a column, they've included a fairly basic function called SUM, as in =SUM(A1:Z1)

Anyway, I'm writing just to say, Excel has a lot of power, a LOT, but as you've discovered, it can backfire if you don't take the time to actually read a little bit. When you get a result that doesn't make sense--as you were--go back and look at the description (use the "Help" menu and search for the name of the function used)....

To make you feel better here, I've been using Excel since (in a sense) before it existed. I began programming on mainframes in the early '70s, moved quickly to PCs when they became available, used in succession, Lotus 1-2-3, Quattro (few remember it), and then Excel in all its generations. BUT, here's where you can find some relief, until your question was posted here, I'd never heard of the QUOTIENT function (it's relatively new). So thank you for adding to my knowledge of Excel.
That's done the trick. Thanks!

R

@mathetes 

'Relatively new' is, by definition, a relative term!  I found a list of functions that indicates that QUOTIENT existed and was not newly introduced in 2010.  Like you, I only came across the function much later and it took a while to realise that it performs an integer divide.  Now it is a regular part of my repertoire used to unpivot crosstab tables and arrays.  Used with a base 1 index k, the formulas

= 1 + QUOTIENT( k-1, n )

= 1 + MOD( k-1, n )

provide row and column indices for the 2D lookup.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies