I have a spreadsheet that keeps attendance. Now I need to calculate the
units (1 unit= 15 minutes) the children are actual here. On a normal
schedule they are here for 16 units but if the are late the units get
subtracted. When entering the data, they would like to use letter instead of
numbers.
I am making an IF function that calculates and converts units, for example kg to g and g to kg etc.
This is the formula I typed in
=IF(E6=kg, ((F6/D6)*G6/1000), ((F6/D6)*G6))
When I evaluate it I get the #NAME error. What do I need to change to make it work?
I am attempting to calculate a dollar value that is based on a number of units. Each unit has a different dollar value depending on how many units there are. for instance,
if the spreadsheet reports one unit, then I need to return $45
if the spreadsheet reports two units, then I need to return $65 or 1st unit at 45 and 2nd unit at $20.
if the spreadheet reports three units, then I need to return $ 75 or 1st unit at $45, 2nd at $20 and 3rd at $10
all subsequent units 4 and up need to add $10 per additional unit.
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.
But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
What I am trying to do is take entered data in a spread sheet, and convert the units within that cells.
I have several entries for data in this sheet. Here is what I want to do:
-I have 104 entered in cell B2
-I want to convert that reading (which is ºF) to ºC in that same cell
-I would like a button or selection from a drop down box to select Metric or Imperial
I want to do this so if the whole spread sheet is entered in metric, I can select a name or click a button to have all the data switch to imperial. I know I can do this with two sheets in the same file, but I am not sure if this is possible to do in a cell I entered the data in. Any help would be great. I am using Office 2003.
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):
Cost per unit for 0-10 units: $1
Cost per unit for 11-20 units: $3
Cost per unit for 21-30 units: $5
Cost per unit for 31-40 units: $8
Fixed cost: $100 (this is regardless of the number of units produced)
Basically I'm looking to construct a cost vs units graph.
I have been tasked with tracking the expected income for our Advertising Department. (I work for a newspaper.) The rate I charge an ad depends upon the total number of advertising units a customer purchases during an entire billing period. Essentially: I need to have a total of column G for when column B matches the current row. I need this in column K. The value of K should be the same when the value of B is the same.
My programing flow chart I sketched out checked to see if any row in column B that is above the current row matched the current row's data. If it did then it would use that row's value for the total number of units. If it did not it would add up the units from G when B matched the current column. I attached a document
Refer to the attached sample workbook,
(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)
Cell F19 should contain =D19*B19
Column 1- These values will not change and represent price/cost of each UNIT
Column 2- "UNITS" the quantity or number of items at column1 value
Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2
Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed
Ideally speaking, As few units in one row as possible to allow for a good mix.
For example: What i don't want is $9000*86 for 774k.
If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.
I believe a Most Even distribution of units will work fine.
After the distribution/population of fields in column2;
D29 must equal F3
F29 must equal F7
I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.
The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.
My code below comes up with #VALUE! error msg
Function PIRR(NumPer As Single, StartVal As Double, EachVal As Double)
Dim Values() As Double
Values(0) = StartVal
For i = 1 To NumPer
Values(i) = EachVal
Next
PIRR = Application.WorksheetFunction.IRR(Values())
End Function
I have three columns. Lets assume i have One header row and 3 rows of actual data and the 4th row is for totaling column B values.
Column A is a col of "Ingredients", column B is the amount of the ingredient (from col A) used. The value is either in Oz or Lbs. Can be either. Column C is a yes or no column. If the value is Yes then i want to have the value in col-B included in my overall sum which is in the 4th row. If the value is No then i do not want the value in col-b (in that row) included in the overall sum.
Any ideas on how to do this?
Thanks.
david
i have been trying to write an Excel sheet in 2007
that can be given a random amount of (money) and show
me the best way to spend it.
Weapons:
Name - Power - Cost
1 - 10 - 20
2 - 20 - 40
3 - 40 - 80
4 - 80 - 160
5 - 160 - 320
6 - 320 - 640
So i have 40K to spend, i want to eneter 40000 into a box and it
will auto populate the number of units i can buy, always setting the weapon
#6 as the priority.
I have lists of room areas in metres squared, I extract the web based lists to excel: for example.
10.56 m2
14.23 m2
21.34 m2
How do i calculate the total of these amounts?
Is there a formular to remove the "m2"?
I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0.
If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together.
Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered.
In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4.
Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?
I have a column named length and values are in different units ie few values in mm, few in cm, few in m. i have to filter out columns having range from 9mm to 6cm. How to do this task.(10mm = 1cm and 100cm = 1m)
Consider sample data as:
4 m
8 cm
9 m
3 mm
9 mm
6 m
6 cm
3 cm
2 m
I'm trying to set up a spreadsheet that tracks total hours worked and total
units produced. Then I need to have a column that shows how many units per
hour were produced.
Currently, I have something like this:
Column A is in elapsed time [h]:mm
Column B is a Number with two decimal places
Column C divides Column B by Column A
However, I get strange results. For example:
Column A is 6:24:00
Column B is 13
Column C shows 120.00
13 parts in 6:24 hours should be something like 2.1666 parts per hour!
Can split a single cell within excel to two or three units of the single cell?
View 2 Replies View RelatedI need a formula that will tell me what % of the first 5 units sold are new, based on the dates provided. This is an example of the data I am working with. What i am looking for is a cell that will respond with .4 (meaning 40% of the first 5 units are new) ...
View 9 Replies View Relatedi need a formula that will return the total value of units which is greater than 20000 in col b between 5 and 30 days
AgeUnits272,472,215.29134,354.83112,714,300.007-112,566.006-239,853.006-105,938.006-36,792.006-608,695.001827,632.007-21.00
I have a formula to add the volume (units) for a customer. The formula is:
=E5+E10+E21+E31+E38+E52+E65+E69
Is there a formula that I can use instead of the one above that will find the customer number and automatically sum the units?
I have a workbook that calculates a RAND function based on input in another cell. Specifically, the formula in E2 selects a random text string from another range depending on data entered in D2. I want to be able to fill this formula down for use multiple times.
The formula works fine on its own, but the problem comes when I, say, move on to D3 to input more data. E3 does its random calculation as it should, but E2 also recalculates; I want E2 to remain static once it calculates the first time.
I'm attaching the workbook in question. The calculation I'm referring to is in the 3rd worksheet.
TLL Injury Machine.xlsx
I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is $.01 off sometimes.
I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.
Option Base 1
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Sub General() 'Makes general calculations
Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatırım Nakit Akımı
Dim FIRR(49) As Variant 'Yearly FIRR
Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet
End Sub
I need to formula to covert duration between start date and end date to units below.
Start Date
End Date
Number of Month
Number of Years
Number of Days
6/1/13
8/31/13
[Code] .........
I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.
It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.
I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.
I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.
I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.
View 11 Replies View RelatedI'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.
The problem is this:
The total $ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.
So I have:
A: total quote $
B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A')
C: Type - '1' or '2' (if '1', then 'C' = $1000), (if '2', then 'C' = $1600)
D: final result
I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:
Sub Test_click()
DOB1 = #7/19/1930# 'DOB
date2 = #7/18/2012#
Date3 = #7/21/2012#
age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82)
age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct)
End Sub
I have a matrix w/ alpha characters in a 10x10 grid. I want to see how many times each letter shows up and then rank them asceding.
What is the function to do this? I looked at Frequency and Count, but do not think either will do the required work.
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
I have a dataset of students' grades (see sample below) which I need to compute the aggregates for.
Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.
Rules for computing aggregates:
1st subject: EL or HMT
2nd subject: H1 or H2
3rd subject: M1, M2, S1 or S2
4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used)
5th & 6th subjects: Any other 2 not previously used
I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.