I'm trying to set up a water bill calculator and am having troubles. We have a base rate that we charge for everything under 4000 gals. After that we have an increasing tiered rate. I'm trying to make a form so we can calculate bills accordingly. I don't know how to create an "IF" scenario.
example
If "total usage" is greater than 4001 but less that 8000 multiply by $4.50 per 1000 gal
If "total usage" is greater than 8001 but less that 12000 multiply by $4.75 per 1000 gal
If "total usage" is greater than 12001 but less that 16000 multiply by $5.25 per 1000 gal
If "total usage" is greater than 16001 but less that 20000 multiply by $5.50 per 1000 gal
If "total usage" is greater than 20001 and over multiply by $5.75 per 1000 gal
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.
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.
I have made an extremely simple chart of bills in Excel. I want it to show each day how many days it is until the monthly due date of each bill. If next to the column of bill names I make a column filled in with single numbers to represent what date of the month each bill is due, can I make a SIMPLE formula to put in the next column that generates the number of days it is until the bill is due?
I am trying to make a bill of materials using excel. The material is 10' sticks of pipe. What I want to accomplish is have a column (column A) with the lengths used in inches and have it show me the total amount of pipe used keeping in mind that once you hit 120" you have to start a new stick of pipe.
For example: If I used 40 inches I have 80 inches left over... But if column A read (40", 90", 60") I need it to know that the 40" will be cut from one stick, the 90" will need to be cut from a new stick, and the 60" can be cut from the left over of the 40" cut.
My customer makes 6 different widgets, all of which use a different number of the same parts (tabA ~ tabZ) For example, model A has the following: 1 x tabA 2 x tabB 0 x tabC 4 x tabD.
I have a matrix which shows how many of the different parts are in each model. The part number goes down the rows and the model being produced goes across the columns. So this goes from A10:G:21
Now, I have to do a forecast for the next year and I trying to figure out the best way to do it and have each months totals of each part. My forecast has the model numbers going down the rows and the months going across the columns. This is from A1:G7
The way I used to do it is have a line for every part and manually multiply the monthly total by each models per unit and it ends up being a formula that gets longer and longer.
January needs for tabA are (B2*B11)+(B$3*C11)+(B$4*D11) ... February needs for tabB are (B2*B12)+(B3*C12)+(B4*D12)...
When they have 20 distinct models it gets difficult. Also, there may be mid-season changes to the bill of materials that make it difficult to change.
Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.
I am trying to create user form for make bill format for super market example if enter in text box the item name etc. then add data in to excel sheet with excel formulas and if customer buy more then one item when enter another item name in to text box it's need to go with same bill number and when search with bill number it's need show all carrying with that bill number and we can print bill format paper.
I would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:
I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).
Parent Item Item QTY Total QTY
A1234 B1111 5
[URL] ...
I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)
In the attached sheet, I have logged my gas energy consumption (kWh) from 2006 to 2012. I want to analyse my relative consumption over time, on a monthly basis.
However, the bills have been generated by different suppliers for irregular periods (eg. 10-May-2006 to 24-Jul-2006, or 23-Feb-2010 to 6-Mar-2010). So, how can I re-tool my sheet to arrive at rough figures that can be allocated against each month?
Or, if there is no dependability about that method, formulae which allow for effective comparison over time? Let's say, daily?
Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
When a date is entered e.g 1/27/2007 The bill payment dates should all be automatically calculated under the “Date” column. Rules for the date
Rule1. e.g. 1 Month—the date should be 1 month after the date entered (as shown by the simple example 1/27/2007 one month is 2/27/2007 in the sheet)
Rule2 if the date entered is the last day of the month, the calculated date should also be the last day of the month provided it is a working day. E.g 1/31/2007 one month is 2/28/2007. (provided 2/28 is working day) 2/28/2007 one month is 4/30/2007 (provided 4/30 is working day)
Rule3 If the calculated date based on rule1 and rule2 is a non-working day, the date is pushed back by 1 till it becomes a working day. e.g 1/27/2007 two month is 3/27/2007 if 3/27/2007 is Saturday it becomes 3/28/2007 which is a Sunday, another non-working day. So the output should be 3/29/2007
Rule4, under rule3, the date cannot be pushed to the next month. Instead, we push the date forward. In the previous example assume 3/29, 3/30 and 3/31 are all public holidays, by rule3, it will be finally pushed to 4/1. But this becomes another month. So we push forward instead to 3/26 which is Friday. The output is 3/26/2007
Definition of working dates (Monday to Friday excluding the holidays listed in the sheet).........
I am hopeless at remembering birthdays tbh - so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday.
Please see attached - I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old - but '=TODAY()-C16' yields "00 Years 4 Month(s) 12 Days"
Also, 'Leah' has just her birthday - but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())-YEAR(C4))*12),"d")'
I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself.
Here's my conundrum:
A potential customer enters "80802" for zip code and "Solomon" for store.
The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.
i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;
I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5% the next 20,000 is taxed at 6% the next 20,000 is taxed at 7% the next 20,000 is taxed at 8% the next 20,000 is taxed at 9% more than 100,000 is taxed at 10%
I'm trying to do a formula like below
Cell B3 is my taxable amount Cell B5 =IF(B$3>20000,20000*0.05,B$3*0.05) Cell B6 =IF(B$3>40000,40000*0.05,(B$3-20000)*0.05)
This gives me a problem in that for 35k say, I end up with a negative number for the second part in cell B6. My other issue is that each calculation will take up 6 rows on my spreadsheet. I was hoping to set up a function that could do this in a cell, but even the simple stage defeats me at the moment.
Creating a calculator for employees and possibly customers to use to return values based on information inputted. Created a worksheet but thought there may be another way to enter and return the calculated information that may be more user friendly. New to user forms but thought that would work for the user to enter information. Can information be returned from the worksheet to the user form?
I downloaded the calculator toolbar, and for some reason it doesn't look right, it doesn't resemble a calculator layout at all.
The numbers are skewed, as though the layout is in landscape view instead of portrate. I've tried repeately to delete it from the toolbars menu option, with no success. It's become annoying.
im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 -25/12/07).
I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work.
Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence).
How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months).
I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.
I am trying to put together a spreadsheet that works out values based on a persons age or service length.
So in I have the following data in columns starting at B4 and ending at k4:
Date of birth; start date; text; text; text; text; Weekly Pay; Start age; Current Age; Years Service.
The formula I need to provide in l4, m4 and n4 comes from the following rules:
* 0.5 week's pay for each full year of service where age during year less than 22 * 1.0 week's pay for each full year of service where age during year is 22 or above, but less than 41 * 1.5 weeks' pay for each full year of service where age during year is 41+
I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate.
So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined.
Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be.
Not sure if this is do-able but I figured I would try. For a golf league coming up later this year I want to figure out how many Birdies, Pars, Bogies, Dbl. Bogies and Others each (20) golfers have during the season. After each round I would input their scores and I am looking for a program that would look at the score of the hole and the par for the hole and figure out what they got 1 under par = birdie, even par = par, 1 over = bogie, 2 over = dbl bogie and 3 over = other.
I started by creating a simple if statement but it ran out too long and my other issue is adding up the number of birdies, pars..etc for each round. Meaning a golfer can have bogies on hole #1 and #2 and the if statement can take care of that but how would get a total saying the golfer had 2 bogies.
I would do this each week and total the number of each to keep a running total at the bottom of each column. We play on the same course each week so the pars for each hole can be hard coded.
I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list:
10 days from -start date- is: 15 days from -start date- is: 20 days from -start date- is: and so on...
I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.
I've been trying to create a Staffing calculator for a call center. Basically the calculator should be able to add up the number of agents for the next 18 intervals based on the login time that is entered by the user. I've just outlined the functioning below:
Suppose 10 agents login at 8 AM (thus logout at 5 PM), the intervals right from 8 AM till 5 PM should show up the 10 agents. Now suppose 10 more agents login at 9 AM, we would then have 20 agents logged in till 5 PM (since agents logged in at 8 will logout at 5) & the remaining 10 till 6 PM. Thus if we have 10 more agents logging in at 10:30, we will have 30 agents till 5 PM, 20 till 6 PM & 10 agents till 7:30 PM & so on. I have attached an excel file to explain the example & the way the calculator has to be built. It is preferrable that the cells containing the login time aren't fixed, but the user should be able to input any login time in any cell.
Attached is sample data. Target payout is the amount of the base salary a salesman can earn if all goals are reached. Commissions are payed quarterly. Listed on the left are 7 goals, each with a weight of importance toward the target payout amount. The percentage achieved can only be 100% or 0%, nowhere in the middle, although they are allowed to make up a goal later if they do not make it at first. This is where I get stumped. For example, if they don't make a goal in quarter one but make the goal in quarter two plus what they missed in one, they get two quarters worth of commission in quarter 2 for that goal. I am having trouble figuring out the easiest way to go about this.
