I have a spreadsheet that I am trying to use to store data and calculate a few things automatically.
I have the spreadsheet attached here with some fictitious data and I am running into a few difficulties.
First of all, i am calculating the Extra Hours and Extra Kms in coloumn W and Y based on the Slab assigned in coloumn Z.
When I am trying to calculate the Extra Charges Hours, i am running into my first problem. I am doing a lookup into another sheet which defines the price per extra kms. Unfortunately, multiplying that with the corresponding Coloumn W is not working since one holds a HH:MM:SS stamp and the other a currency. How would I overcome this challenge to figure out how much the actual extra kms costs?
The second thing I cannot for the life of me figure out to do is how to avoid the negative values when doing the SubTotal in Coloumn AF. I do not want to use the negative values cause my calculation for the subtotal is based on the highest value of either extra kms or extra hours or both if they are positive, not negative values. Any help on how to figure that out?
The last thing is I cant for the life of me figure out how to create a Report using PivotTables.
Any video tutorials on how to use them to create a Report Structure to list only certain coloumns?
Thanks in advance