How to Best Match Manual Calculator Calculations to MS Excel Calculations

[Susan J]

Hi Everyone


You will see from the enclosed that the formula for O21 is as follows:

((N21*J21)-(N21*J21)*L21))). You will also see the the Excel calculation total vs my calculator total is $499.44 vs $499.80 and $445.72 vs $445.49 for Cells O21 and O23.


The manually calculated total must be used, how best can I do this. I tried using ROUND, ROUNDUP to no avail.


Also, I am a rookie so would appreciate a quite specific response to the solution (ie. How to...) if there even is a solution.


Thanks in advance for your help.


http://www.2shared.com/file/Nt_YCFJ2/CVR-2012_12_11-SEMI-FINAL.html

 

[kchiba]

Hi Susan,


try the following in L21


'=ROUND(IF(LEFT($D21,9)<>"",IF(LEFT($D21,9) = "Flat Rate",RIGHT($D21,3)/100,(IF((($E$7-$G21)/365)*(RIGHT($D21,2)/100)>=75%,0.75,(($E$7-$G21)/365)*(RIGHT($D21,2)/100)))),0%),2)


cheers


kanti

 

[NARAYANK991]

Hi Susan ,


The value of 499.80 is obtained when the depreciation is exactly 2 % ; however , the actual depreciation is a little more than this : 0.0207123287671233


You need to decide whether the depreciation should have 0 , 1 , 2 or more number of decimal places ; depending on this , if you change your formula in L21 as kchiba has already posted , you will find that the values as calculated by Excel and your calculator will match.


However , I have a something to add :


You are calculating the values in column E as : =IF(D21<>"",(RIGHT(D21,3)),0)


However , these values are not used by any other cell in the worksheet ! Your formulae in column L can easily use this ; instead of the existing :


IF(LEFT($D21,9)<>"",IF(LEFT($D21,9) = "Flat Rate",RIGHT($D21,3)/100,(IF((($E$7-$G21)/365)*(RIGHT($D21,2)/100)>=75%,0.75,(($E$7-$G21)/365)*(RIGHT($D21,2)/100)))),0%)


you can have :


=IF(LEFT($D21,9)="",0%,IF(LEFT($D21,9)="Flat Rate",$E21/100,MIN(0.75,((($E$7-$G21)/365)*E21)/100)))


Narayan

 

[Susan J]

Hi Narayan


Thanks for the ROUND formula (it works!) and for the formula modification. I, too, think that some of these forumulae are "wordy" and shall consider changing.


Susan