Help with Excel Calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1514809%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514809%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20boxes%20with%20numbers%2Fsums%20in%20them%20everyone%20giving%20me%20the%20answer%20I%20expect%20(as%20copying%20from%20a%20work%20slide)%26nbsp%3B%3C%2FP%3E%3CP%3EApart%20from%20this%20one%3A%3C%2FP%3E%3CP%3E164.27%20%2F%2029%20*%2017%20%3D%2096.30%20excel%20is%20giving%20me%2095.28%20as%20the%20answer%20(see%20red%20box)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20really%20frustrating%20as%20I%20need%20my%20work%20sheet%20to%20be%20the%20same%20as%20the%20example%20I'm%20copying.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%26nbsp%3B%20also%20be%20better%20if%20i%20could%20condense%20the%20different%20calculations%20-%20do%20you%20have%20an%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20advise%20please%20-%20many%20thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1514809%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514937%22%20slang%3D%22en-US%22%3ERE%3A%20Help%20with%20Excel%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514937%22%20slang%3D%22en-US%22%3EIssued%20solved%2C%20it%20was%20to%20do%20with%20rounding%20-%20had%20to%20'set%20precision%20as%20displayed'%20in%20advance%20option%20-%20happy%20days!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514955%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725054%22%20target%3D%22_blank%22%3E%40AlisonAMK%3C%2FA%3E%26nbsp%3B%20Your%20%22problem%22%20is%20round%20off%20errors%20or%20in%20this%20case%20carry%20through%20or%20higher%20precision.%26nbsp%3B%20If%20you%20want%20to%20force%20the%20round%20off%20at%20various%20points%20use%20ROUND(%20N%20%2C%26nbsp%3B%20X)%20where%20N%20is%20the%20calculated%20value%20and%20then%20X%20is%20number%20for%20digits%20of%20precision%20to%20keep%20before%20rounding%20off.%26nbsp%3B%20So%20in%20your%20case%2C%20by%20changing%20F6%20to%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DROUND(D6*5%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20changing%20E8%20to%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DROUND(5*D8%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ethe%20answer%20now%20displays%2096.30%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%20I%20really%20don't%20understand%20why%20you%20are%20doing%20what%20you%20are%20doing%20per%20se.%26nbsp%3B%20For%20example%2C%20what%20is%20the%20justification%20for%20wanting%20to%20force%20these%20round%20off%20cuts%3F%26nbsp%3B%20Especially%20when%20you%20are%20dividing%20by%20365.2425%20in%20the%20first%20place%20to%20get%20so%20many%20sig-digits.%3C%2FP%3E%3CP%3EAs%20for%20making%20the%20calculations%20easier%3F%26nbsp%3B%20It%20all%20depends%20on%20what%20values%20you%20need%20(either%20to%20see%20or%20for%20other%20calcs%20elsewhere).%26nbsp%3B%20You%20CAN%20create%20NAMEs%20to%20define%20particular%20values%20and%20then%20your%20calculations%20can%20look%20like%3A%20%3D%20Annual%2FTotalDays%26nbsp%3B%20instead%20of%20%3DC6%2F365.2425%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515071%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515071%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20i%20have%20no%20clue%20about%20excel%20-%20the%20reason%20for%20the%20calculation%20is%20to%20do%20with%20payroll.%20The%20software%20provider%20has%20shown%20their%20version%20in%20a%20demo%20which%20was%20what%20i%20was%20trying%20to%20put%20into%20excel%2C%20hoping%20that%20all%20i%20needed%20was%20the%20salary%20and%20hours%20to%20input%20and%20the%20rest%20would%20sort%20itself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20how%20to%20do%20your%20suggestion%2C%20but%20will%20have%20a%20go.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20again%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi 

 

I have several boxes with numbers/sums in them everyone giving me the answer I expect (as copying from a work slide) 

Apart from this one:

164.27 / 29 * 17 = 96.30 excel is giving me 95.28 as the answer (see red box)

 

Its really frustrating as I need my work sheet to be the same as the example I'm copying.

 

Would  also be better if i could condense the different calculations - do you have an suggestions?

 

Appreciate any advise please - many thanks in advance

3 Replies
Issued solved, it was to do with rounding - had to 'set precision as displayed' in advance option - happy days!

@AlisonAMK  Your "problem" is round off errors or in this case carry through or higher precision.  If you want to force the round off at various points use ROUND( N ,  X) where N is the calculated value and then X is number for digits of precision to keep before rounding off.  So in your case, by changing F6 to:

=ROUND(D6*5,2)

and changing E8 to:

=ROUND(5*D8,0)

the answer now displays 96.30

 

That said I really don't understand why you are doing what you are doing per se.  For example, what is the justification for wanting to force these round off cuts?  Especially when you are dividing by 365.2425 in the first place to get so many sig-digits.

As for making the calculations easier?  It all depends on what values you need (either to see or for other calcs elsewhere).  You CAN create NAMEs to define particular values and then your calculations can look like: = Annual/TotalDays  instead of =C6/365.2425

 

@mtarler Thank you for your reply.

 

As you can see, i have no clue about excel - the reason for the calculation is to do with payroll. The software provider has shown their version in a demo which was what i was trying to put into excel, hoping that all i needed was the salary and hours to input and the rest would sort itself.

 

I'm not sure how to do your suggestion, but will have a go.

 

Many thanks again for your help.