Varying Price Points Based On Hour Intervals
Jan 18, 2014
I have a client where I have agreed to a variable price per hour depending on how many hours is used.
So the first 40 hours of a month costs X USD
The next 40 hours of a month costs Y USD
The next 40 hours of a month costs Z USD
The next 40 hours of a month costs Q USD
And all hours above 160 in a month costs T USD
All hours are registered as decimal with 15 minutes as a minimum, so 0.25, 0.5, 0.75, 1, 1.25 etc.
Now the challenge for me is to set up a formula calculating the total cost based on how many hours is registered each month.
I.e. if I register 46 hours in one month the first 40 hours should be multiplied by X and the last 6 hours by Y. And if I register 173 the calculation looks like this:
40 * X
40 * Y
40 * Z
40 * Q
33 * T
And I can't figure out how to make the formula calculate based on what is between the various intervals.
View 3 Replies
ADVERTISEMENT
Sep 26, 2008
08:00 26calls
12:00 50calls
16:30 16calls
The problem is the data is output as above just outputing intervals with data.
I need to convert this data to 15 minute intervals as below:
08:00 13calls
08:15 13calls
12:00 25calls
12:30 25calls
16:30 8calls
16:45 8calls
View 9 Replies
View Related
Aug 7, 2013
I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER
Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))
View 2 Replies
View Related
Jan 8, 2014
So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?
So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.
View 5 Replies
View Related
Jun 28, 2012
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x
504 that show up 3x
24 that show up 12x
View 3 Replies
View Related
Oct 2, 2013
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test1.Test1
End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test2.Test2
End Sub
etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub
View 7 Replies
View Related
Feb 20, 2008
I have a spreadsheet set up that has a series of timestamps and values. Please find a copy attached. I need to set up a macro that will allocate the timestamp in column C to an interval of time, e.g between 12:30 - 13:00.
To achieve this I tried to set the following code up in VBA, which would input a letter in a blank cell based on the time interval that the timestampin column C falls into, for example if the timestamp is > 12:30 and < 13:00 then "A"...the plan was to extend this concept to accomodate all of possible the time intervals I have.
Sub time_intervals()
Dim x As Integer
Dim lastrow As Long
Dim timeA As Date
Dim timeB As Date
Dim timeC As Date
Dim timeD As Date
Dim timeE As Date
The idea was that the timemaster value would be the timestamp in column C of my spreadsheet and the timeA,timeB,timeC etc would reference the timeintervals. However this does not seem to work as the value returned by my if statement is always incorrect - for example timemaster in cell C2 is less than timeA but the if statement always returns FALSE as opposed to "A"
View 4 Replies
View Related
Sep 4, 2006
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the date/time off column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the date/time off on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
View 9 Replies
View Related
Oct 21, 2013
I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.
I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.
For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.
Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?
A
B
C
D
E
1
2
4
2
2
In
Out
8 am
9 am
10 am
[Code] ...........
View 3 Replies
View Related
May 30, 2012
SUM formula. My spreadsheet has roughly 8000 rows of data listing client info for how many commercials were purchased on Cable Networks a (a simplified example)...
ABCDE1ClientNetworkDay-partPrioritySold2Client AHGTVAccess35063Client BHGTVPrime45014
Client CHGTVDaytime30095Client AHGTVPrime45096Client DHGTVPrime35037Client BHGTVPrime5502
What I am attempting to do is on Sheet2 summarize the commercials sold based on a starting Priority. So if the priority code in Column D is 350, I need to sum all spot sold at a priority of 350 and higher for each day-part and Network
ABCD1NetworkDay-partPrioritySold2HGTVPrime350 and up
?3HGTVPrime400 and up ?4HGTVPrime450 and up
?5HGTVPrime550 and up ?6HGTVPrime600 and up ?7HGTVPrime650 ?
I've attempted to create helper columns to tie all the necessary PR codes together and then SUMIF by the different helper columns, but I'm not getting the correct result. Instead of getting "15" for Sheet2, D2... I'm just getting "3"
View 5 Replies
View Related
Nov 12, 2013
Is it possible to take data in format dd:hh:mm and convert to working days - ie. 8 hour days?
For example:
02:12:15 (60 hours and 15 minutes .. or 3615 minutes)
to be converted to:
07:04:15 (7 working days: 4 hours: 15 minutes)
View 3 Replies
View Related
Dec 2, 2013
I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.
Attached file, defined example and detail.
View 1 Replies
View Related
Sep 5, 2012
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
Windows 7 Ultimate / Excel 2010
View 1 Replies
View Related
Jun 9, 2008
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.
View 2 Replies
View Related
Apr 21, 2009
I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03
On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.
View 5 Replies
View Related
Sep 17, 2010
I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.
I couldn't find a lot of info but I also may not know where to look.
example:
if (b2<62) then price is ($sheet2.b2)
if (b2<124) then price is ($sheet2.c2)
if (b2<372) then price is ($sheet2.d2)
Is this even possible to put into a formula?
View 6 Replies
View Related
Jun 26, 2012
I'm using a spread sheet to determine what I should charge customers. I want to make at least $300 profit on every job. If my profit is not at least $300 then I must up my price until profit reaches $300. Currently I'm manually adding a percentage (Factor) to the original price until I reach $300 profit. In the image below:
A is the original price
B is the factor by which I multiply the original price to increase it
C is the new figure which is reached after multiplying (1+B2)*A2
D is the costs related to that job (I enter these manually on a separate sheet within the workbook)
E if the profit (C-D)
I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.
Now what I would like to do is if E2.
View 2 Replies
View Related
Jan 1, 2008
Trying to create a Math formula that allows for Taking advantage of sale pricing.
In Excel the following:
Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), =
Extended Price(L3)
Example
2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00
Not quite sure how to handle the Sale price taking over regular pricing
View 9 Replies
View Related
Jan 4, 2010
The analysis basically has 2 data components to it:
The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.
The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.
What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:
1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)
2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)
3) Pull the price for this "closest quantity"
I have uploaded a worksheet showing the structure of that data.
[url]
Is there some VB code I need to do this, or can it just be a few simple formulas?
View 4 Replies
View Related
Jan 21, 2009
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
View 9 Replies
View Related
Jul 25, 2011
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
View 2 Replies
View Related
May 2, 2007
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
View 2 Replies
View Related
Jun 18, 2013
I have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched
I have attache the file for example : Sale monthly.xlsx
View 9 Replies
View Related
Jul 6, 2007
I'm a graphic designer and i'm trying to build a spreadsheet that will help me in doing my accounts.
Ok, so i'm trying to set up a function that will generate a price based on three fields:
Lamination:YES or NO
Duplex: YES or NO
Stock: Around 14 different stock options.
I had it working using a nested IF but I ran out of the 7.
I then tried using CONCATENATE using "&" to seperate but excel said the formula was too long.
View 14 Replies
View Related
May 22, 2013
1 TO 10 25.00
11 TO 50 15.00
51 TO 100 9.00
101 TO 250 5.40
In this there is an area where it is cheaper to buy for example 12 instead of 10 and I am trying to work out a formula to deal with this funny step change down as people buy more.
View 2 Replies
View Related
Apr 23, 2009
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged.
Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution.
Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View 9 Replies
View Related
Apr 17, 2014
I have actual date , then I have names of customer and I have product reference. And what I want to find out is the latest price COLUMN D for which this specific product was sold to specific customer (Data in rows E,F,G,H are from another tab). So basically I want is to find out that latest price of product x27 sold to customer A on 15.7.2013 was 70 because on 12.7.2013 we have sold it to customer A for 70.
A
B
C
D
E
F
G
H
[code]....
View 5 Replies
View Related
Nov 22, 2007
I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.
Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.
View 7 Replies
View Related
Nov 8, 2011
I'm looking to find a accurate average based on some data points. The issue is that some service times are 0 and so are considerably larger than the norm. So...
17
18
12
13
14
12
10
5
5
0
0
Assuming the following data 12,13,14,12,10 are probably the most accurate and all other are not.
View 7 Replies
View Related
Dec 9, 2011
I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.
=IFERROR(VLOOKUP(C4,'Dunnage Exception'!A:C,3,FALSE),IFERROR(VLOOKUP('Raw Data'!D4,'Dunnage Master'!A:C,3,FALSE),"0"))
View 2 Replies
View Related
