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.
I want to call on the existing rand() function as a part of the function i've written, however I'm getting an error saying sub/function not defined when I go to compile. How do I have to define rand() so that vba knows that I want the use the existing function?
I need to use a function that is not onyl random, but only uses a person's name on a list once. On a list of ten people, RANDBETWEEN picked the same person three times in ten key strikes. How can I use RANDBETWEEN but also make sure ever person's name gets picked ONLY once? This is for a drawing for secret santa.
When I use the =RAND() function, everytime I do anything within my spreadsheet the number changes. Is there a way to get that number to change after(for example) 10 refreshes of my spreadsheet instead of every single time I do something to it?
I have been using the RAND and Vlookup functions for the following without success.
I have 4 numeric Pools or Groups; 1. A1:A18 (1-18) 2. B1:B18 (19-36) 3. C1:C17 (37-53) 4. D1:D17 (54-70)
I also have E1:E20 (20 existing Numbers from 1-70)
I want Excel to select at random a total of 20 numbers and place them in F1:F20.
I will specify a quantity from each of the 4 groups. Eg. In cell A20 I enter 4 to obtain 4 numbers from grp 1, In cell B20 I enter 6 to obtain 6 numbers from grp 2, In cell C20 I enter 8 to obtain 8 numbers from grp 3, In cell D20 I enter 2 to obtain 4 numbers from grp 4,
However there must not be any duplicates between E1:E20 and F1:F20!
Is there a way to control the number generation. I would like to be able to run different scenarios with the same set of random numbers, then regenerate the random numbers and do it again. Right now every time I do anything in the spreadsheet, I get a new set of numbers.
I'm making a custom BINGO board in Excel 2010 for a family reunion, so it doesn't contain numbers; instead it has words (like "has brown eyes" or "served in the military"). the first sheet is the board and looks like a traditional BINGO board with the same number rows and columns with a center FREE space. On the second sheet is the list of all the phrases I'm going to use, one phrase per cell, all in Column A.
In BINGO cells, I want it to use one item (phrase) in each box but NOT repeat within the board. I have the following code in each cell and it works EXCEPT, there is no code telling it to make each cell unique, so sometimes it may repeat.
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 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 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.
I'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 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.
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
I currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?
I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx
What would the formula be to calculate that array of values = 57% because 4 of them are equal to or greater than 9, and there are 7 of them in total? The formula should accommodate however many numbers are listed and calculate what percentage of them are 9 or 10.
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)
The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0)
This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
I want to obtain from some elements the number of cells it takes to appear: We have for example A,B,C, and D, and they appear in the next order:
1A 2C 3D 4A 5D 6B 7C 8A 9A
What I want to know is how much last in appear each element.
1A1 2C2 3D3 4A3 5D2 6B6 7C5 8A4 9A1
For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?
For example,
Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009
Start Date: ??? Finish Date: 8 Jun 2009 Duration: 5 days
The Start Date should be 30 May 2009.
Seems like I am not able to use WORKDAY function to calculate the start date.
I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.
Here is what I invision(cells are for example purposes)
=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))
So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.
I would also do this with a minimum outlier as well, but i can set that up if this first one is possible.
