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

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

- Excel help - Calculating Stamp Duty
- Changing the number of cells calculated
- Stamp Duty Formula
- Group sums of column data
- Database of customers + invoice, how to?
- Use Cells To Define Formula Start/End Rows
- Please help with nested if formula
- Help with formulars percentages etc
- OLAP for Excel
- generate word file from excel
- Index Match Index Formula work slow
- How do I clear data in selected cells automatically
- Using excel to figure costs if variable in many different ways
- Hiding Rows without any numbers.
- Is it possible to use custom vba functions in pivot tables calculated fields formulas
- Custom calculation in Pivot Table
- XL97: Calculation Problems with Arrays in Custom Functions
- XL2000: Calculation Problems When Custom Function Creates a Defined ...
- Custom Function May Not Calculate Expected Value
- Calculate running daily/monthly customer totals with given acquisition and attrition rates
- How do I calculate duty times
- XL2000: Calculation Problems When Custom Function Creates a Defined ...
- Worksheet Calculations Exclude Custom Formula
- Formula does not calculate numbered customer accounts

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

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

=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.

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.

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.

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

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

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

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.

Question:

Can excel generate or fill up word document automaticaly ?

if yes, How ?

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.

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 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!

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 - - - - - - - -

Thanks in advance

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

Thanks,

Dave

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 ...

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.

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

their overtime

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 ...

worksheet calculate event (i.e. Activesheet.Calculate) on custom functions.

Is this possible?

Thanks in advance

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 !