How To Get Monthly KWh Usage When Bill Data Spans Months
Jun 8, 2013
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?
View 3 Replies
ADVERTISEMENT
May 29, 2014
i am working on model where customer is billed based on usage of the equipment. Usage is measured in cycles per day. Assuming there are 30 Days a month. i am trying to build a spreadsheet where if i increase or decrease the monthly usage it automatically update the sheet. Remember there is limit on maximum number of times an equipment can be used.
Like for Example
Maximum life of one equipment. 3000 Cycles
Daily usage : From 1 cycle per day to 10 cycles.
Monthly usage = 30 Days * (daily usage)
Now the problem is each month new equipment is leased. so i have to keep track of each month's equipment how much it has been used and how much left. I have attached the example spreadsheet.
View 1 Replies
View Related
Jan 11, 2013
I'm trying to create a monthly forecast over 9 months. I know the planned cost for month 1 and month 9 but need to figure out how to spread the remaining amount over months 2-8 using a downward slope.
Here's the problem:I have 9 months to spend $28M. I know in month 1 I plan to spend $6M and month 9 $3M. That leaves $19 for months 2-8. I need to figure out what the burn rate would be in months 2-8 ramping down from $6M to $3M and not exceeding the available amount (19M)
Is there a way to caluclate this in Excel?
View 1 Replies
View Related
Mar 13, 2009
I have a large amount of information. I need to use three criteria to look up one value however the data now spans across two tabs as there arent enough coulmns to support the data in one tab. I have been trying to use the following:
View 6 Replies
View Related
Aug 3, 2009
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
View 9 Replies
View Related
Mar 6, 2013
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
View 1 Replies
View Related
Dec 29, 2006
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?
View 9 Replies
View Related
Sep 9, 2009
I need to create a chart that will show month over month sales for different channels. But each month total is in a separated tab in the same file.
Example:
August totals
Channel Sales
Channel 1 $1000
Channel 2 $2000
Channel 3 $500
I need a chart that will show sales for Channel 1 ,2 and 3 from jan to aug.
View 8 Replies
View Related
Dec 20, 2006
I need to calculate time period worked between 19h00 and 24h00 in following exemples,
start time 08h00 and End time 20h00, would be 1 hour
start time 20h00 and End time 08h00, would be 4 hours
I can't get IF fonction to work with times..
View 9 Replies
View Related
Jul 24, 2007
I'm trying to write a code to define different months and shade their backgrounds but i can't seem to get it to work.
The fields are set out as follows:
01/01/2007 13:31
The code i have at current is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:Z1000")) Is Nothing Then
Select Case Target
Case 1 / 1 / 2007 To 31 / 1 / 2007
icolor = 6
Case 1 / 2 / 2007 To 31 / 2 / 2007
icolor = 7
Case 1 / 3 / 2007 To 31 / 3 / 2007
icolor = 8
View 9 Replies
View Related
May 27, 2008
I'm trying to do some calculations involving times. I'm using the format [=A2+(A1>A2)-A1] in order to calculate times from one day to the next which avoids negative numbers. This is working well. My problem is now that I'm trying to develop my spreadsheet and am trying to embed this inside an IF statement as the [value_If_True]and I get an error because it doesnt like the leading equals sign inside the IF statement.
View 6 Replies
View Related
Mar 21, 2007
I need to make formulas to count the number of weeks that each phase of a project takes, but I currently have the start and end dates of the phases. (See attachment)
View 5 Replies
View Related
Sep 15, 2007
Hide Multiple rows. I have text within two columns A, D. How can I Hide Blank rows which spans two column Named Ranges, "Range1" (A1:A15) "Range2" (D1:D15). As an example:
Beginning with this:
...A......D
1.Text
2........Text
3.
4.
5.
6.Text
7.Text
8.
9.
10.......Text
11.Text
12.
13.......Text
14.
15.......Text
To this , after hiding blanks:
...A......D
1.Text
2........Text
6.Text
7.Text
10.......Text
11.Text
13.......Text
15.......Text
View 2 Replies
View Related
Mar 22, 2012
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.
View 1 Replies
View Related
Nov 20, 2007
I am trying to do parts forecasting.
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.
View 9 Replies
View Related
Sep 17, 2012
Any way to calculate the total hours staff work based on the mininum time of the first transaction to the maximum time of the transactions. I used a DMIN and DMAX function to get those times per employee. The issue is then the time goes over from one day to the next, such as from 11 PM to 4 AM the next day. As you can see in the data below,the fourth record shows the minimum time as 12 AM and the max as 11 PM with total time worked as 23 hours. In this example, the total hours worked should be five hours.
min time
max time
total hours
7:00 AM
16:00
9:00
[Code] .......
View 4 Replies
View Related
Apr 30, 2014
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
Is there an easy way to do this?
View 8 Replies
View Related
Feb 9, 2010
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
View 14 Replies
View Related
Dec 5, 2009
I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:
U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID
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.
View 9 Replies
View Related
Mar 23, 2011
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.
View 4 Replies
View Related
May 14, 2014
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??)
View 3 Replies
View Related
May 8, 2014
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112
Value 2345678910111213
And get it like:
Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
View 11 Replies
View Related
Jul 24, 2009
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.
Would a macro make the task more automated?
View 9 Replies
View Related
Jan 27, 2007
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).........
View 3 Replies
View Related
Dec 6, 2013
I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
View 6 Replies
View Related
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
Jan 27, 2014
I am trying to convert quarterly data to monthly
The quarterly data columns go: Mar-14, Jun-14 etc
The monthly data columns goes Jan-14, Feb-14 etc
I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14
Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?
View 2 Replies
View Related
Jan 31, 2014
I have a table which has the following columns:
Date - Data1 - Data2 - OtherData1 - OtherData2
I came up with some formulas to count my data monthly. I have 12 tables with this kind of formula in it:
[Code] ....
Where B12 is the year and A213 is my month number. My first try on the "date filter" looked like that:
[Code] .........
And it wasn't working so I thought it was because the 31 wasn't a good idea for non-31-days-months but none of the formulas above are working.
(BTW, IDK why it's not working but I have data in my table for months 10, 11 and 12 and the only calculation tables that are calculating data are the ones for months 9 and 10. The results are the same in these two tables and are counting all my Table1[Data1] and [Data2] (the count is not monthly))
View 7 Replies
View Related
Sep 29, 2007
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
View 3 Replies
View Related
Jan 27, 2014
Basically i have month end data ranging from 31/01/2000-31/01/2009 with a value attached to each. I need to convert this data into a daily series with the month end value being the same throughout the whole month. I've been playing around with excel for a couple hours tonight
View 9 Replies
View Related
