I have a total sale price, minus expenses then the following formula is run in 3 different calculated columns to split the Sale Price-Expenses over 3 accounts. The break down for those is 70% 20% 10%. All that changes in the formula between the 3 columns is the % it gets from the sale price-expenses.

=ROUND(IF([DTF/DWI/Admin/Jud]="DTF",([Sale Price]-[Title Transfer]-Towing-[Other Expenses])*0.7, IF([DTF/DWI/Admin/Jud]="DWI",([Sale Price]-[Title Transfer]-Towing-[Other Expenses])*0.7, IF([DTF/DWI/Admin/Jud]="Jud",([Sale Price]-[Title Transfer]-Towing-[Other Expenses])*0.7, IF([DTF/DWI/Admin/Jud]="Admin","$0.00")))),2)

EX: Sale Price=755, Expenses=130.19 formula takes 755-130.19=624.81*70%=437.367 and so on for the other percentages.

So these 3 columns + expenses should = the sale price but I keep getting + or - one penny most of the time. How do I fix this? Do i round out further I have tried up to 6 places and it is still off.

  • please share some examples for the current result and the expected result! – Mohamed El-Qassas MVP Oct 16 '18 at 22:06
  • To add to this more, as you can see in the formula the classifications are based on on the value of another Column. Some calculations only use 2 of the 3 accounts but here are a couple of examples. – Gary Peters Oct 17 '18 at 13:26
  • Sale Price=760.00 Expenses=166.75 so here is the calculations being done 760-166.75=593.25 Then 593.25 is split into 3 accounts in a 70%,20%,10 split 593.25*.7=415.28 and 593.25*.2=118.65 and 593.25*.1=59.33, So then i should be able to add the expenses plus all three accounts to = the original sale price 166.75+415.28+118.65+59.33=760.01 I end up with an extra penny. I have tried using ROUNDUP, ROUNDDOWN and ROUND and it will fix some entries but not others. I 'm not sure what i need to do to make this constant. If I use ROUNDDOWN I will end up -1 cent. – Gary Peters Oct 17 '18 at 13:40
  • There is another version of the formula that is used based off a condition that only enters values into 2 accounts in a 70% 30% split but the results are still the same + or - one cent when the calculations are done. – Gary Peters Oct 17 '18 at 13:44

What I ended up doing was rounding one column up and one column down and then just used Round for the third column... Might not be the correct way to do this but it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.