I'm trying to calculate a price for any given mileage. The pricing strategy is as follows:
For any journey 2 miles or less, the price is £9
For any journey between 2 or 3 miles, the price is £10
For any journey above 3 miles, the price is £10 for the first 3 miles plus £2 for every additional mile.
So, for example, the price of a 1.5 mile journey is £9 and the price of a 3.5 mile journey is £11.
I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet.
I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.
I have attached the workbook I am working with : Production Report.xlsx‎
i have a data in excel
In cell B6 , I have an amount . and in Cell C6 I have inserted the date at which that amount has been kept . Now what i want is I want the Data from D6 To O6 to calculate as to how many Days that 100000.00 Rs or Dollar have been kept each month . Each month has its own Days like January has 31 Days . So i want each Days to appear accurately. Like for example if i insert the 4-8-2009 from January till August 4-8-2009 , how many days that amount has been kept each month .
I have to write a calculation that sums a range of values. Times that sum by a given amount but only if the total of the original sum is greater than a specific criteria -
A
B
C
D
E
F
G
H
I
J
K
L
M
PRODUCT
[Code] ........
In the above example it calculates the total units sold of Product A, by Month, by 5.00.
In Column M, It totals the number of sales units and then also totals the number sales units by that given value 5.00. So 426.5 x 5 = 2132.50
I need a sum that only totals the value over a given criteria.
EG. Total 426.5
Criteria > 300 = 126.5
Sum would equal 126.5 * 5
Attempting to calculate assignment hours based on three fields -
A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)
I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5
My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.
One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
I have 5 cells with drop down validation lists for the different types of window that we offer. From this list I will select the options and then input some dimensions in cell dimension fields. I then have a cell that gathers the text string from the 5 drop down cells.
My aim is to be able to calculate glass sizes from the text string and the dimensions. Each of the possible text strings will require different calculations and will define the sizes that I need to order. I know the equations that I will need for each of the options but cannot think of how to make them dependable on the text string cell.
I am looking for some way of using IF function to say if the text string returns as certain text then there is a certain calculation to calculate from the dimensions for the glass sizes that I need. I dont know if I've explained it too well nor even if its possible but if it is possible I will find a way as I have done some pretty exceiting things with excel in the past.
Criteria
Column B
Constant
Variable0
Variable1
Variable2
Variable3
2
[Variable2/Constant]
5
123
43
45
76
[Code] ..
This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?
I require calculating difference between two columns. (Actual and Estimated).
Difference between these two numbers are calculated in third column, but it is reset every time actual number is non-zero.
Scenario:-
-Estimated Numbers are always followed by an actual number.
-After actual number is obtained, estimated numbers till that date will be added and deducted from actual number.
-After this, again estimated number will be added till next actual number is obtained for deduction.
If estimated number is not followed by actual number, then no need for difference calculation.
How calculation can be made based on the date. I have 2 sheet (MATTER and REPORT). All productions made within sheet MATTER. Then I will produce report to be printed in sheet REPORT. I only include the date and calculation every matter will be automatically made based on date and item.
View 6 Replies View RelatedI need a formula that I can place in, lets say, Cell A1 that will reference Cells B1, B2, B3 and C1, C2, C3. These all have numbers & %'s in them. However, I want to "weight" the B cells by the percentages in C cells. The problem I am having is that when I change the %'s nothing is changing in the output...almost like I was doing nothing but averaging B1:B3 without the % to adjust.
This is how I envision it, but my calculation may be wrong of course :
A1 = average(sum(B1,B2,B3)*C1, sum(B1,B2,B3)*C2, sum(B1,B2,B3)*C3)
The Goal:
This would allow me to change the output depending on the percentage. So if I am looking at days to an appointment and the B cells represent areas of a city: B1 = 10, B2 = 5, B3 = 2 the average would be 5.6, however, I know that B1 city has more "weight" to it with our customer base so I want B1 to be closer to what will show as days out (which is obviously important as 10 days is not good). So I would assign C1 = 60%, C2 = 30%, C3 = 10%. This should pull the average more to 7 or 8 as it would lean more towards the B1 value. I hope this makes sense.
I did try going more of a route where I did something like =average(B1*C1,B2*C2,B3*C3) but that lowered all the original values instead and averaged them instead of pushing the number more towards the higher weighted % of B1 cell value. I can send an example spreadsheet if I am losing anyone.
Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well
I tried all those search and have no idea what to look for, sorry.
I have attached a single sheet with Footpath Budget for you to see.
In J6, we put in Y or N for Yes or No if there's a nature strip along the footpath.
Now, in cell G49 gives us the total square meters of nature strip.
What I want to do is if I put N in cell J6, cell G49 should be empty or zero because N means there's no nature strip. But if I put Y in J6, then cell G49 can give us total squares from cell C43.
So what formulas can I put in cell G49?
I have the following spreadsheet as an example:
A
B
C
D
E
F
G
H
1
Article/hours
10
27
33
15
33
40
40
[Code] ........
Column A contains codes for certain work activities.Row 1, from Column B and beyond contains number of worked hours.The matrix is populated with letter "D" to mark activity for particular work code for particular amount of worked hours.
My goal is to have a formula/script that analyzes this matrix, and returns a total amount of worked hours per work code. In other words, if a cell contains the letter "D", the amount of hours in Row1 for that particular column should be added to the total amount for that particular code, let's say code "501" as an example. For this example, the result should be a cell, lets say "A8", that contains the total amount of hours spent on code 501: in this example - 213 hours.
i have a sheet which has name of students (A2:A10), B1:E1 has project name. and the projects are catagorised by color red=high,green=medium, yello=low. i have given weightage to these color red=80%, green=50%, yello=20%.
similarly the involvment of the students in each project is shown by these three color in respective cell. like B2 is red, C2 is red,D2 is blank, E2 is green. (the weightage is same as above). in column F i want the total utilisation of each student, which is simply sum of product of weightage given to these colors.
e.g.
project 1project 2project 3project 4utilisationsam1.38joe0.54nick0.72
what i want is to calculate this utilisation number, but i want my sheet to be shown in the same color format.
i am sure that there is some solution for this.
I have been trying to put this together but am confused since I am new and this seems complex to me.
This is what I am trying to accomplish:
IE.
$F6 = 100
If $I6 is >1 AND $I7 is = 1 AND $I8 is =1 AND $I9 is =1 then $J6 =$F6
If $I6 is >1 AND $I7 >1 AND $I8 is =1 AND $I9 is =1 then $J6 =.5*$F6
If $I6 is >1 AND $I7 >1 AND $I8 >1 AND $I9 is =1 then $J6 =.33*$F6
If $I6 is >1 AND $I7 >1 AND $I8 >1 AND $I9 >1 then $J6 =.25*$F6
I am trying to divide a dollar amount equally between "Officers" if they were part of a transaction. By selecting the officers it would divide the total amount equally.
Is this even possible?
I'm trying to create an if statement to check to see if a cell has a value in it and return a math function. My problem is that some of the values contain more that just the name searched for. This is a basic example
A B C
10 Williams DXT if(B1="Williams",A1*.8,if(B1="Johnson",A1*.85,1))
20 Williams DPX if(B2="Williams",A2*.8,if(B1="Johnson",A2*.85,1))
30 Johnson AMV if(B3="Williams",A3*.8,if(B1="Johnson",A3*.85,1))
40 Johnson ACS if(B4="Williams",A4*.8,if(B1="Johnson",A4*.85,1))
50 Smith TWN if(B5="Williams",A5*.8,if(B1="Johnson",A5*.85,1))
in cell A1 the user will select from a drop down list 5.50, 11,22, 33, 55. in cell B1 user selects from the drop down list a number 1 - 9. now in cell c1 I wont it to do some calculation based on the selections.
If b1=3, then A1*.20, if B1=2, then A1*.30, If B1=1, then A1*.50 and if B1 is greater then 3, post 0, if B1 is empty, then leave c1 empty.
I need an excel function for this...
Say,
Cell A2= 8
Cell A3= 1
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B
September: 26c
October: 13.5c
November: 43c
December: 66c
e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)
What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)
I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
For the past month now, I'm trying to calculate some work hours (night hours actually), based on a reference.
Let me show you the table:
- Column A has all of the schedules of the employees
- Column B has the numbers of hours for each schedule (all have 8.5 hours/day)
- Column C has the reference for the night hours.
Now what I'm trying to do, is to find a formula that will calculate the numbers of the night hours using the reference in Column C
Row 23 in that table has an example of what I'm looking for.
how to make a cell to change color oatomatically accordant to Year and Month reflexted already in the cell. I have a spreadsheet with Names,Surnames,DOB and also a column that is given the AGE years months and days.
If somebody reaches 21 -1 -2 -3 months this should show different colors in the persons row in a particular column. The function used for the AGE is fund on ozgrid.com: =DATEDIF(H4,TODAY(),"y")&" years "&DATEDIF(H4,TODAY(),"ym")&" months "&DATEDIF(H4,TODAY(),"md")&" days"
I want to copy all rows from my input sheet that have a date less than the date in cell B3 of the output spread sheet and are contained in the stock table (range A7:B17) Sedol in column B Is a unique identifier. I started to do this but am lost.
View 6 Replies View RelatedI'm quite a novice at Excel. I have a column of values that I sum as follows;
A
0
0
0
0
0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedI have a cell A1 in sheet2 linked cell A1 in sheet1 (simply A1='sheet1'!A1). A1 in sheet1 is a data validation drop down menu.
I want to clear the content of A2 in sheet 2 everytime the content of A1 in sheet2 changes/is updated. That is everytime the value of A1 in sheet1 is changed using the drop down menu.
I tried using a Worksheet_Change event macro (which I do not fully understand) but it won't work with a cell that updates from a calculation. It also doesn't work if triggered from a cell from another worksheet (I tried linking it to cell A1 on sheet1 in this case).
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A2").ClearContents
End Sub
Any simple solution to clear the content of cell A2 in sheet2 when A1 in sheet2 updates?
I am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.
View 5 Replies View Relatedmake a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.