SOLVED

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
Highlighted
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
Highlighted

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

Highlighted
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
Highlighted
Solution
Hi

So per the screenshot you would type

=F55/G55
Highlighted
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.
Highlighted
That's done the trick. Thanks!

R
Highlighted

@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.