• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Mega Complex formula required or VBA for calculation related to huge database code

RAM72

Member
Hi all

As header specified I have a tax database code which starts from 00000001to 99999999. I have simplified the database for more ease on the combination that are involved .

They are 8 digits code which allocates as per item product their taxation %,however they are preference tax code which are applicable to certain goods origin which may be % or figures .
Some general tax value are in % others in figures. the first calculation starts on the landed value whatever the currency.
It continues on Excise _1 and Excise _2 rate which may be in figures or % ,these depend on different manual data imput as per annexed sheet except for some vehicles items which are in % .

The excise rate 1& 2 may have value or only one rate may be applicable that is excise rate 1 is applicable and excise 2 not applicable or both may not be applicable depending on Tax code number

I have put all the combinations possible to make it soft as much as possible as it very complicated and messy for me.
All data in green are manually imput, those in black is retrieved from another database ,the blue data one is expected to be done automatically with expected results at column Y for total payable.

There are formula in the cells to explain how it works for calculation.You can use the auditing tool bar to see how it works as it very complicated .
My expectations is that when I feed data in column A, J, K, L,M,N. it does the calculation automatically based on database tax base sheet with possibility to drag endless.


See annexed.
thanks :(:(in advance
 

Attachments

  • Calculation code.xlsx
    20.1 KB · Views: 0
I would do what I think you have done
Calculate all possible tax and rates in extra fields (columns)
Don't worry that some won't be used in some cases (rows)

Then use a Lookup Function to extract the values you need based on the Tax Code

I did a similar thing recently where there were 3 rates for just one component
There was a Base Rate and 2 other rates which were 200% and 250% of the Base rate
So you can just calculate this once and use arithmetic in the summation etc
 
Back
Top