Free Microsoft Excel 2013 Quick Reference

Calculate Customs Duties

Hi,

I need help with a formula. I use excel to create my invoices. I want to calculate the Custom Duties when I enter the the total cost of an item in my excel sheet.

Insurance is calculated first, to find out the insurance on an item it is 1% of the Cost (after calculating and the figure is less than a $1 then a $1 is used but if it is over a $1 you will use that exact amount), when done add it to the total. Then multiply that figure by 15% (just an example percentage) then add $1 again. See an example below.

Cost of item (Car Part) = $21.00
Shipping = $20.09
Total = $41.09

1. 1% (insurance) of $41.09 = 41 cents (if the amount is lower than a $1 then you use a $1 but if it is over $1 then you would use that exact figure)

2. Add them: $41.09 + $1 + $42.09

3. 15% of $42.09 then add another $1

So the Custom Duty on the car part will be $7.31

Thanks,

Wayne


Hello all,

As a novice to excel, this is a great forum. Thanks everyone!

I was hoping someone could help me with the following:

1) Calculating Stamp Duty

Please see attached jpeg of Stamp Duty rates for sales of property in Hong Kong. Stamp Duty is based on the "consideration" or purchase price of the property.

I tried to create a formula by using nested IF functions but couldn't finish it because of the 7 nested formula limit. How could I go about solving this in a neat, clean manner?

Essentially, I want to be able to input the purchase price and have it automatically output the relevant Stamp Duty.

Thanks for your help!

Excel Amateur

Hello Friends!!

I want to ask if it is possible to make the CORREL function to calculate custom periods of data. For example CORREL(E52:E181,F52:F181) calculates correlation for 129 rows of data (181-52). Do you think that the range that the function takes into account can be instead represented as the number of the current cell minus a cell N rows above (e.g. CORREL(current cell’s address in column E – N rows:current cell’s address in column E, current cell’s address in column F – N rows:current cell’s address in column F)) and the N will be set in a separate cell? For example for N=10 the function will look as follows CORREL(E171:E181,F171:F181) and CORREL(E151:E181,F151:F181) when the N is set to 30.

Hoping for an answer))

Dima

I have current formula to calculate stamp duty in an invoice.

=ROUNDDOWN(IF(C1>250000,PRODUCT(C1,3%),IF(C1>125000,PRODUCT(C1,1%),0)),0)

but i need to add an extra bit to calculate possible first time buyer exemption

IF(C3="Y",0,0)

Where abouts would i put this? Do i need to add any other parts to it?

Any help, much appreciated.

hi all,
is it possible from an extremely long single column of particular invoice amounts listed consecutively to do like a group sum (that is "fill down" a formula in a new column) considering that the number of rows for the particular invoice amounts vary - see below

i have about 600 invoices each with either 1, 2, 3, 3, 4 or 5 rows of particular consecutive amounts (rows) to group sum & show as individual invoice totals in a new column (like INV_TOTAL).

DATE INV AMOUNT INV_TOTAL
X........1........10
...................30.........40
Y.........2.........5..........5
Z........3..........7
....................15
....................10
......................2........34

i require this for customs as they think i owe them customs duty & require duty differences (my calculation of duty and theirs) for each invoice.

many thanks.

Hi, I’m in the process of writing my first invoice system for a company, and as I’m not an expert in Excel I would to ask someone kind to give me some advice on how I should deal with my problem. I’m not good with formulas, and as I’ve seen this is the place to ask, there I am

Basically my system operates on 3 sheets:
Control – Used to make invoice, writing the company address etc.
Invoice- This is where all the magic happens and all the numbers go from the control panel. All linked with formulas for calculations.
Customers- This is the bit where I’m struggling. What I’m trying to create is a customer database, where theres ID numbers next to them 1-20, and whether you put in Control Panel the ID number of the customer, it will fill the details of a customer for you.

My question is, how can I link a database of customers that will with a drop down list format and link it to the invoice system.

Hello Friends!!

I want to ask if it is possible to make the CORREL function to calculate custom periods of data. For example CORREL(E52:E181,F52:F181) calculates correlation for 129 rows of data (181-52). Do you think that the range that the function takes into account can be instead represented as the number of the current cell minus a cell N rows above (e.g. CORREL(current cell’s address in column E – N rows:current cell’s address in column E, current cell’s address in column F – N rows:current cell’s address in column F)) and the N will be set in a separate cell? For example for N=10 the function will look as follows CORREL(E171:E181,F171:F181) and CORREL(E151:E181,F151:F181) when the N is set to 30.

Hoping for an answer))

Dima

I have a nested if formula which is supposed to calculate stamp duty on a sliding scale and I just cannot get it to work (must be a super dumb day).

Cells have different numbers based on other calculations but cell B8 = 67,320 and the answer should be 1068.1

The formula is
=IF(BA81400030000800003000001000000,(BA8-1000000)*0.055+40490))))))

Any help with syntax and/or other errors I have made would be much appreciated.

Thanks, DeniseL

Hi all
Have been going mad trying to work out some formulars
Have used help section with no joy.
Ok have a few ?

I am making up an invoice page.
I have 5 columns
one being product number, item description, customer cost, commision paid and gst
Now what I need is in the commision column say commision is paid at 20% of the customer cost. I need the formular to work this out so when I enter a cost for the customer the commision amount column automaticaaly works out the commision and places it in that box. Hope I still am making sence.

Also I need the same for the GST column. I need to beable to work out what 10% is automatically in the GST column once I have entered a customer cost

Now with all of this I need to beable to transfer these formulars down the bottom also.
I have done the customer cost one, but am unsure how to do the rest.
I need to beable to calculate customer cost in one, then take away the commision in another, have a total amount paid for GST then have the final cost.

Hope all this makes sense

Cheers
Kate

Does anybody know of an OLAP-esque tool that would be a little more robust
than Pivottables? The limitations of Pivottables that I am trying to
overcome are getting %'s within subtotals as well as the way Pivottables
calculate custom formulas. Pivottables assume a SUM function in formulas.

I am using microsoft excel to calculate customer quotations and then type all number in microsoft word document manually.

Question:

Can excel generate or fill up word document automaticaly ?

if yes, How ?

Hellow friends,
I have using Excel 2007.

I creating two sheets. (Sheet1 & DATA)

In DATA sheet : Total 20,000 row record. (sorted : column "C" by order A to Z & column "B" by order newest to oldest) column "C" is code & "B" is date.

In Sheet1 : I have calculate customer wise recorder. i.e. number of quantity sold & last date of transaction.

for example : In F6 position I enter formula =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$F$2&":",DATA!$N:$N)
In Sheet1 : A6 is code F2, G2, H2, I2, J2 is series.
In DATA sheet AI column created a key with two columns (=":"&C2&"::"&Q2&":") code & series & column "N" quantity.

Same formula in column : G6,H6,I6,J6
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$G$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$H$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$I$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$J$2&":",DATA!$N:$N)

Now
in column K6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$K$2=DATA!$Q:$Q),0),0))
in column L6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$L$2=DATA!$Q:$Q),0),0))
in column M6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$M$2=DATA!$Q:$Q),0),0))
in column N6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$N$2=DATA!$Q:$Q),0),0))
in column O6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$O$2=DATA!$Q:$Q),0),0))

In data sheet column B is date, column C is code, column Q is series.
In sheet1 : column A6 is code, column K2,L2,M2,N2,O2 is series.
In sheet1 total data row is approx 3000.

My problem is : when i selecting filter & selecting any type of data i.e. > or < or any data record. it will take calculating / processors time more than 15 to 20 minutes its work very slow.

Is there a way to faster calculation.

I have attach sample file with some record.

I am writing a program to do mortgage calculations customized to my needs.So
far I have been able to do this with built in functions and some of my own
formulas.

I need to be able to clear the input fields quickly when working with a new
customer.Is there a way to clear designated fields just by setting up an
clicking a drop down box?

thanks in advance for your help.

Jim Train

I'm not sure that my title is the greatest, but here goes:
I am trying to use excel to help me calculate snow removal cost for 30 different customers. We have 4 basic contract types, but each customers price is different depending on the size of their property. Pricing also changes for each contract type at a different rate depending on the total snowfall. I have all the data and variables in a separate "Rate Table" and a Template for event reporting. I want to be able to input the total snowfall and have excel populate the amount fields in my copied worksheet correctly so I can use that sheet to generate invoicing. I'm just not sure what my "holy grail" formula will be to get the amounts right. here are 2 examples of the kinds of differences:

Customer A has a standard contract. for 1-3" of snow he pays $25.00 for 4-6" he pays $35.00 for 7-9" he pays $45.00 etc. (until 12"+ then it changes to an hourly rate(which I'm not teriibly worried about having excel calculate)
Customer B has a Standard plus contract. for 1-4" she pays 40.50 and $10.00 per inch after that. (the 12"+ hourly rate applies to all customers)
Customer C has a Premium contract. 1-6" they pay $42.00 and $10.00 per inch after that. (with the hourly rate at 12"+ )

If I get 5" of snow then the following is true
Customer A owes $35.00
Customer B owes $50.50
Customer C owes $42.00

But if I get 7" then the following is true
Customer A owes $45.00
customer B owes $70.50
Customer C owes $52.00

I hope this is enough information.

Thanks in advance for any help!

We currently dump our full accounting trial balance (which can have inactive or rarely used accounts) to an excel file. This excel file then feeds into many other areas of a financial statement package. Because the actual Trial Balance file in Excel is part of the financial package, I don't want all the account rows to show if they don't have any numbers. For example see below: I want to hide the rows that don't show any numbers such as COGS: R&A Defective, R&A ENtry, R&A Pricing, etc. Is there a way to select an area and have excel hide the inactive rows automatically without me selecting each row manually?

COGS: R&A Damage 511.20 0.02 - - 511.20 0.02 - -
COGS: R&A Defective - - - - - - - -
COGS: R&A Entry - - - - - - - -
COGS: R&A Pricing - - - - - - - -
COGS: R&A Sample 92.10 - 163.73 - 92.10 - 163.73 -
COGS: R&A Whs Packing 25.11 - 254.01 - 25.11 - 254.01 -
COGS: R&A Whs Shortage - - 50.56 - - - 50.56 -
COGS: Customer Rebate 15,709.67 0.51 29,172.03 0.55 15,709.67 0.51 29,172.03 0.55
COGS: Vendor Rebates - - - - - - - -
COGS: United Dues - - - - - - - -
COGS: Material In Process - - - - - - - -
COGS: Inventory Adjustment 337.06 0.01 - - 337.06 0.01 - -
Inventory Adjustment 10,637.36 0.35 822.16 0.02 10,637.36 0.35 822.16 0.02
Inventory Adjustment Purchasing - - - - - - - -
COGS: Closeout Inventory - - - - - - - -
COGS: Over/Short A/P - - - - - - - -
COGS: Merchandise Free Goods - - - - - - - -
COGS: Spiffs 541.81 0.02 - - 541.81 0.02 - -
COGS: Sales Waiver - - - - - - - -
COGS: Incoming Freight - - - - - - - -
COGS: Incoming Customs Duty - - - - - - - -
COGS: Incoming Other Fees - - - - - - - -
COGS: Incoming Handling 15.00 - - - 15.00 - - -
COGS: Outgoing Freight - - - - - - - -
COGS: Outgoing Handling - - - - - - - -
Spoilage: Damage/Defective - - - - - - - -
Spoilage: Damage Vendor Credit - - - - - - - -
Glidden Upcharge - - - - - - - -
Depreciation: Commercial Books - - - - - - - -
Purchases 2,374,198.33 77.35 5,361,329.82 101.06 2,374,198.33 77.35 5,361,329.82 101.06
Purchases: Consignment - - - - - - - -
Purchases: Work Order - - - - - - - -
Purchases: Direct - - - - - - - -
Purchase Order Handling In (809.04) (0.03) - - (809.04) (0.03) - -
Freight In 295.00 0.01 1,509.36 0.03 295.00 0.01 1,509.36 0.03
Import Freight Variance - - 7,302.50 0.14 - - 7,302.50 0.14
Recovery Freight In - - - - - - - -
Clear Freight to Inventory - - (70,533.04) (1.33) - - (70,533.04) (1.33)
Clear Purchases To Inventory (2,374,198.33) (77.35) (5,361,329.82) (101.06) (2,374,198.33) (77.35) (5,361,329.82) (101.06)
Clear Retired Rental to Inventory - - - - - - - -
Clear Consignment to Inventory - - - - - - - -
Clear Work Orders to Inventory - - - - - - - -
COGS: Mgt Fee - - - - - - - -
Road Show Spiff - - - - - - - -
Show Spiff 5,724.14 0.19 - - 5,724.14 0.19 - -
Promotional Spiff - - - - - - - -
Sales Order Freight In - - - - - - - -
Sales Order Handling In - - - - - - - -
Purchase Order Freight In (Billabl 1,678.29 0.05 34,938.89 0.66 1,678.29 0.05 34,938.89 0.66
Transfer Order Freight In (Billabl - - 5,413.31 0.10 - - 5,413.31 0.10
Transfer Freight Out (Billable) 8,731.83 0.28 (614.85) (0.01) 8,731.83 0.28 (614.85) (0.01)
Clear Comm-Cost Difference to P&L - - - - - - - -
FSP D/A Discount - - - - - - - -
Transfer Handling In - - - - - - - -
Transfer Handling Out - - - - - - - -

Hi there!!! I was wondering if it's possible to use custom vba functions in pivot tables calculated fields formulas, I don't know if I was clear enough because English is not my first language, but what I need is to use a custom function I created in vba in a pivot table calculated field formula.

Thanks in advance

I get the concept of the custom calc in a pivot table. I just cannot make it work. In this case, I just want to get a Count of the number of records that match my summarized data. I click on Formulas | Calculated Field and I enter a name "Count of Cases", then in the formula, I enter =COUNT(Account) where Account is the AccountID. My std fields are summing by Sales Rep just fine. But I only get a '1' in the Count of Cases field for each Sales Rep.

I consider myself a pretty good excel user, but this problem has me stumped!

Thanks,
Dave

XL97: Calculation Problems with Arrays in Custom Functions

When you use a custom function in a workbook, the function may return incorrect values. Recalculating the workbook may also take longer than it does in earlier versions of ...

XL2000: Calculation Problems When Custom Function Creates a Defined ...

You should design your custom functions so that they only use the return value to change the value or text of the cell that called the function.

Custom function may not calculate expected value in Excel

I am trying to calculate the number of customers I will have at the end of each month and year, given data for acquired customers and annual attrition rate. I then need to calculate annualized monthly revenues associated with each customer based on how long they've been a customer. Here is an example:

I acquire 5K customers every year, assuming equal distribution throughout the year. I have an annual attrition rate of 30% for customers who have been customers for 1 year or less and an attirion rate of 20% for customers who have been customers for more than 1 year. I get $10/month per customer in revenues. What do my customer base and revenues look like each month/each year?

I started by calculating the number of day 1 customers I acquired - 5K/365 = 13.69863. I assumed no attrition on the day they were acquired and a daily attrition rate of 30%/365 for each of the next 365 days they were customers . I applied this rate to the new customer base calculated each day for the next 365 days. Thus, the number of day 1 customers acquired becomes 13.69863-.01126 = 13.68737 customers as of end of day 2. The number of day 1 customer acquired then becomes 13.68737 - .01125 = 13.67596 as of end of day 3. Etcetera through end of day 366. I then apply the lower attrition rate (20% annual/365) to this same group for days 367 through day 1825 (end of year 5). I do the SAME thing for customers acquired days 2-30. In this way I can see how many customers I've gained and lost each day.

My problem is representing this in a nice, clean formula. From an accuracy and simplicity standpoint, I can probably be less detailed and look at monthly (instead of daily) attrition simply by taking the average number of customers I had during the month (beginning + ending divided by 2).

Anyone know a good way to approach this problem and a formula that I can plug in to give me the average number of customers I have each month?

Thanks

How do I calculate duty times and need to separate if any one is late and
their overtime

XL2000: Calculation Problems When Custom Function Creates a Defined ...

You should design your custom functions so that they only use the return value to change the value or text of the cell that called the function. NOTE: If the custom function passes ...

I want to exclude certain custom calculations calculating when I use the VBA
worksheet calculate event (i.e. Activesheet.Calculate) on custom functions.
Is this possible?

Thanks in advance

Hi,

I'm struggling with the following calculation that someone created for me.

=LOOKUP(10^10,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!$C2:$C13=$A2,IF(Sheet1!$B2:$B13-DAY(Sheet1!$B2:$B13)+1=B$1,Sheet1!$D2:$D13)))))

My problem is that I have some customer account numbers that are alpha/numeric (1B0012) and some that are just numeric (1675) . The above formula does not pick up the numeric accounts correctly.

Is there something that I need to add/remove from this formula so that it will pick up both ? or is it a format issue ?

I have attached an example of the spreadsheet with this problem. If you select either of the alpha numeric codes it works, but if you select the numeric code it doesn't.

Your help is greatly appreciated !