Formula Entering Zip Code And Returning Tax Rate From Established List

Oct 29, 2013

I am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.

VBA Code For NPV Function With Variable Interest Rate?

Feb 2, 2014

I would like a function which works like the NPV function except that the interest rate should be variable and a range rather than a single number.

Is it possible to see the code for the excel NPV function so that I can modify it?

Or alternatively has seen such a function?

VBA Code For Entering Dates

Jun 13, 2013

I need a VBA code for entering dates. The start date cell is A15.When I select cell A15 and hit macro button it inserts todays date and fills in 1-month of dates in column A excluding the weekends...

Compound Rate: Annual Growth Rate %

Jun 4, 2007

The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.

r = 100((Y/X)^(1/n))-1)

So for X = 200, Y = 750, n = 10, we have

r = 100((750/200)^(0.1))-1) = 14.1309%

Activating VBA Code When Entering A Cell?

May 9, 2014

I am trying to write a small bit of VBA code, so that I can record the row and column of the active cell. I have got it working when I CHANGE the value of the cell, but I need to get the code to fire off when I ENTER the cell.

The code section is below:-

Private Sub Worksheet_Change(ByVal target As Range)
'Store the row and column into G1 and G2 for the drop down loading
Call RowNum(target)
End Sub

[Code]....

how I can do this, as I cannot find reference to a Worksheet_Enter function ?

Entering List Of Data Using VBA?

Aug 1, 2014

I have a list of number I want VBA to add to a sheet. right now i have a VBA to add it one by one but was wondering if there was an easier way.

EX.Range("A1").Select
ActiveCell.FormulaR1C1 = "78"
Range("A2").Select
ActiveCell.FormulaR1C1 = "275"
Range("A3").Select
ActiveCell.FormulaR1C1 = "280"
Range("A4").Select
ActiveCell.FormulaR1C1 = "765"

RATE FORMULA

Aug 23, 2007

I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.

VBA Code To Lock Cell After Entering Data

Feb 2, 2014

I have a worksheet, i want to lock the data entered in a cell of a particular column . e.g. if i will enter the data in A2, A2 should be locked, when user enter data in cell A3, A3 should be locked. I want this code to apply on multiple columns (A,C,E,G).

Entering List Depending On A Cell Value?

Feb 6, 2014

i have 5 lists of names which i want the sheet to enter depending on the cell value of C1. C1 is a drop down of 5 different departments so when C1=department 1 for instance i want A4:A25 to automatically update the list of names that i have against that department.

im sure its fairly simply but i just cant get any formula i use to work!

Entering Data From Top / Beginning Of List

Feb 10, 2012

I am trying to create a list of customer credits for my store. I have the person's name, the date of their credit, and the amount of the credit. This list will eventually be substantial and I would like to know how I can set it up so that when I open the worksheet I can enter new names from the top of the list and have the rest of the list shift downwards. This would stop me from having to scroll to the very bottom to add new info every time.

I would like to have it set up this way so that eventually I could put a search query at the top of the page (i'm thinking of rigging something up using vlookup) so that when my employees want to access the data base they can easily search a name and have the relevant info brought up, or can easily type in new store credits.

Lock Cells Automatically After Entering Via Macro Code

Apr 23, 2008

I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.

Formula To Put The Grade And Rate Beside Each Name

May 12, 2007

FromToGr Rate
90100A14.0
8689A23.6
8085B13.2
7279B23.0
6471B32.8
5663C12.6

This is part of a marks to grade and rating table. I have a list of students and the marks they obtained in an exam. I need a formula to put the grade and rate beside each name e.g. John; 92 marks; A1; 4.

Flow Rate Formula

Oct 2, 2007

Looking for a formula that would calculate flow rate (GPM) based of ID of pipe & PSI.

Example - 3/4 inch ID pipe at 70 psi = GPM

Formula - Calculate New Base Pay Rate?

Dec 27, 2012

I am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%

So here are the columns used:

L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is \$12 and Max \$18 and my new base pay is \$16 -- then I am good. However it if is \$11.50 I need the formula to return at least \$12. And visa versa -- if the new rate would be \$18.50, I need it to return no more than \$18.

Compound Interest Rate Formula

Jan 27, 2007

I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula

r = n * nt root (A/P-1)

where;
r = the effective interest rate
n = the number of times interest is added per year
t = the total number of years
A = the current value
P = the original value

The 2 problems I face are;
1. Confirming this formula would provide the correct answer (need maths expert here) &
2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel

Limitations Of VLOOKUP: Entering Data From The Long List

Feb 28, 2007

Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?

First Code In VBA: Use A Inputbox For Entering The First Date For Monday Of The Month Each Time I Use The Worksheet

Jun 23, 2006

I am writing a module which have a different test based on for each of the previous 2 column cells. It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i need to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell. is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again.

Formula For Calculating Warehouse Picking Rate?

Mar 11, 2014

I am trying (and failing miserably) to create a fomula to 'score' my pickers in a warehouse environment.

The basic variables are;

1) Units (Total quantity of units on picklist)
2) Lines (Number of different products on picklist)
3) Time (Total time to complete pick)

To give an example, if Units = 8, Lines = 1 and Time = 00:01, I want 'Score' to = 100.
(Based on benchmark, rounded down to lowest common denominators)

The added complication I face is the relationship between Lines and Units; it would take someone longer to pick 1 unit from 10 lines than 10 units from 1 line.

Formula That Will Provide A Weighted Average Rate

May 14, 2009

I would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....

Formula To Be Able To Give The Full Time Rate

Oct 5, 2009

I have a spreadsheet, but I need the following formula to make our lives easier here.

I have the following columns:

Rate, I/T/P/S, FT/PT

I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94

How To Calculate Trend Line Growth Rate (as Annual Percentage Growth Rate)

Feb 13, 2014

From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.

Macro To Create A Dialog Box For Entering And Updating Drop Down List?

Oct 31, 2013

This is what I'm trying to do:

1. When a user clicks on a Drop down box and selects "Add New", I would like a Macro to run

2. I want this Macro to open up a Dialog/Text box to say "Enter New Information" where the user can enter in some text

3. The text that is entered by the user into this box will be added as a new entry into the Drop down box selection

My VBA knowledge is still at a beginner's level and I know how to get a Macro to run based on a cell value. However I don't know how to create a Macro for the dialog box nor do I know how to create a Macro for updating the drop-down items.

Creating Formula To Lookup Rate Using Ranges And Other Criteria?

Mar 26, 2013

I need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:

User would input the following information:

Zip Code: 56559
Pallets: 3
Weight: 1200

The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".

From Zip Code
To Zip Code
Pallets
Weight From
Weight To

[Code]......

Formula To Validate Invoiced Rates Against A Rate Card?

Mar 23, 2014

We have agreed maximum rates with suppliers for certain services and I'd like to check that the rates they have invoiced fall below the agreed maximum.

I've attached an example of the data I'm working with. What I'm after is a formula that will cross check the details and rate charged on the 'Invoiced' tab against the three 'rate card' tabs and generate an output that flags any discrepancies. I've added a column called 'Validate' on the 'Invoiced' tab where I'd like this formula to go.

All the data should match with the exception of the shift which is listed as a description on the rate card but is a concatenation of the job role and an abbreviation of the shift (D = Days, N = Nights/ Saturday and O = Sunday/ Bank Hols).

Formula That Looks At Trade And Time And Then Puts Correct Rate

Mar 5, 2014

Column A = persons name
Column B = number of hours
Column C = trade
Column D = shift (as in day, night or weekend)
Column E = I would like the formula that looks at the trade and time and then puts the correct rate.

I have a little table on the site with trades/times and rates. IF and IF THEN do something

I took a screenshot to show: [URL] ..........

Excel Formula For Cell Reference When Only Know One Value And One Big Rate Chart

Mar 27, 2014

I have been trying to work on this excel worksheet for a while. I have give the charge column (tab:data) and the rate chart (matrix type with weight and zone). I want to find the formula that would tell me if the charge is in the rate chart (I know it would use countif), and tell me what the zone and the weight associate with it. Using

=CELL("address",Index()), but it won't work.

Using Dropdown List While Entering Data Via Excel Built In Form Function

Jan 19, 2013

how to add a drop down list while using the excel form for data entry.

Basically I am trying to make it easy for a non technical person to add ebay items to a spreadsheet. This sheet is them used in sixbit software to upload to ebay.

All of these items will have some constants (such as length of listing or listing type etc) but selected from a small list i.e they can ONLY be certain times or types for example . So a list is very important to make sure there are no errors but also for speed.

The sheet has a number of headings so when I select the form function in excel the box comes up with the desired entry boxes just fine but I cant get a list to select from even when I have already created a drop down list in excel sheet.

Error When Entering Formula Using VBA?

May 29, 2014

I have a formula that I'm using which works and gives me the right value.

Code:
=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)

However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA.

Code:
ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"

Entering A Formula As An Array In VBA

Oct 20, 2008

My VBA sub contains
DIM sFormula As String
DIM rCell as Range

at some point in the code, the value of sFormula is set to something like
=ROUND(SUM(\$A\$5:\$A\$10*\$B\$5:\$B\$10),2)
(not really this formula but of that flavour)
later in the code I have an instruction
rCell.FormulaArray = sFormula
(rCell is set to a single cell at the time)

When I run the code and then examine the cell, I find that the cell's formula has been correctly set to
=ROUND(SUM(\$A\$5:\$A\$10*\$B\$5:\$B\$10),2)
however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.

Formula To Calculate User Fees Based On Variable Rate Table

Nov 15, 2013

I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in \$0.40 per user, for users 1-4M it will bring in \$0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be (\$0.40 x 1 million) + (\$0.30 x 3 million) + (\$0.20 x 3 million) + (\$0.10 x 2 million). I know the answer to the problem is obviously \$2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)

I've attached an example spreadsheet : Variable Fee Schedule.xlsx‎