When i aggreagte this data is Tableau then the rate per day gets totalled giving an in accurate number
You need to ignore the Excel calulation and do the calculation insideTableau
SUM([Amount]) / SUM([Trip Days])
This will tell Tableau to recalculate at any aggregate level.
Any time you see a "/" (divide) in a calculation, you should do it inside Tableau, with a function ( in this case SUM() ) wrapped around each field...