I'm looking for something one step deeper than a countif. I need something that will tell me what iteration number it actually is within the countif, in relation to date, as shown.
Name
Date
Iteration #
[Code]....
Any formula that could be used to do something like this?
I'm trying to use a variation of the COUNTIF function to generate a total number of occurrences. The RANGE is a singular column on each of five tabs in a spreadsheet, but the CRITERIA (hopefully) is made up of two separate cells on one tab of the same spreadsheet. Here is the formula as I have it written:
=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","8),countif('Tuesday-No Shows'!D:D,"*" & A2 & "*"),countif('Wednesday-No Shows'!D:D,"*" & A2 & "*"),countif('Thursday-No Shows'!D:D,"*" & A2 & "*"),countif('Friday-No Shows'!D:D,"*" & A2 & "*")))
The purpose of this formula is to check to see how many times a student does not show up for an appointment over the course of their time with us. We have one tab with all the pertinent info on each student and I'd like to use this formula for a "No Show" column to more easily know when to cancel a student's services. The problem is that I can only get the function to search for one criteria right now. I've tried using the OR function and adding it to the current function but it always gives me 0 as the result, but if I take out the OR it returns me a higher number. Here's what it looks like with the OR added:
=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Tuesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Wednesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Thursday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Friday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*"))))
A2 designates the students first name and B2 designates their last name. If I eliminate the OR and resort to multiple COUNTIF's, it will generate twice the number of responses that I want, I fear.
Example.xlsx
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
id
event_name
C83-858
sample_1_arm_1
[Code].....
I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
Suddenly I have a problem with copying formulas:
I started with the following formula: =COUNTIF($D2:$D5694,D5724)
I then copied it to the next line: =COUNTIF($D3:$D5695,D5725)
That's not supposed to happen if you use the $ sign. So I checked Tools > Options > calculation. That is set to automatic. What else could cause my problem?
here it is..
500 to 599>>>> equal t0 600
600 to 699>>>> equal to 700
the numbers from equals is the scale of the drawings which i use plot.
sample:
32039.98
31959.50 ...
Is it possible to use the =randbetween() function to generate a random number between 0.8 and 1.2.
View 4 Replies View RelatedI have build a form in Excel. I would like to make one field (ID) to automatically generate the next number.
View 9 Replies View Relatedis if there is a way to generate random numbers between 0001 and 9999. I always need to always have 4 digits. The first couple of numbers can be 0 so that I can maximize the number of variations.
i have worked in access using vba procedures. i can easily create a form in access that generates a random integer number between 0 and 20 and the user will guess the number between 1 to 20 if the random number is 16 and the user enters 14 the program will display a msg box saying too low or too high. here is the code .
Option Explicit
Private Const MAX = 20
Private Const MIN = 1
Private iKey As Integer
Private lTries As Long
Private Function Random() As Integer
Random = CInt(Int((MAX - MIN + 1) * Rnd() + MIN))
End Function
Private Sub cmdGuess_Click()
Dim lGuess As Long
now i have to do the same thing in excel create a program that will ask the user to enter numbers between 0 to 20 and see if the random number and user guess is same or not. but i have never worked in excel using vba procedures how can i achieve the same thing in excel using a vba procedures.
I want to iterate a For Loop changing one variable each iteration until the Total from the For Loop equals zero. Each iteration of the Do Loop keeps the previous Total from the For Loop and adds it. How do I reset the For Loop to start from Total = 0 at the beginning of the For Loop?
Sub PV_IRR()
Discount_Rate = Range("A2")
Total_Investment = Range("B2")
Annual_Return = Range("C2")
[Code]....
I'm wanting some sort of formula to generate a reference number that relates to numbers i put in a cell. i want the end result to look something like this:
Reference number
362 3620001
456 4560001
362 3620002
487 4870001
456 4540002
As you can see id like it to generate a reference number to the number that i input, and also when i input that number twice or three times it picks that up on the reference number also.
I want to get number (going from 1 to 15 (for example)) when i click on commandbutton (on userform without textboxes, combos , etc, ..... except only one commandbutton)!
In other words, when i click on commandbutton, i want to get (on msgbox) number 1, then when i click again on commandbutton, i want to get number 2, then when i click on it third time, i want to get number three on msgbox window, and so on until i close userform!
In column A on sheet1 I have a list of unique numbers . On sheet2 I have a list of unique numbers that are available. I am currently using this formula,
=INDEX(Sheet2!A:A,MATCH(TRUE,INDEX(Sheet2!A:A"",0,1),0),1)
to look at sheet2 and tell me what number is next after I copy it to sheet1. My problem is how do I automatically generate the next number onto sheet1(by double clicking) and have that number removed from sheet2?
each time a button is clicked I want a new ref number to be produced and displayed on my userform.
I can't however create the code to generate this ref number.
In column A of my 'sweeplog' worksheet is where I want the ref number to be placed. This ref number is simply made up of today's date - in format ddmmyy eg 240408 for today 24th April 2008 then add the number '1' if its a new date or the next number in coulumn A if not.
for example In column A I have these 3 ref numbers
A2 = 2404081 (24th April 2008 plus 1 as this is the first ref used today)
A3 = 2404082 (as above but with 2 added)
A4 = 2404083 (as above but with 3 added)
now tomorrow the next ref number that would be shown in column A (after the user clicks the button) would be as follows.....
A5 = 2505081 (25th April 2008 plus 1 as this is first ref used on 25th)
I am able to get this unique ref number to show in my userform but can't figure out how to produce the code to generate it
I have a spreadsheet with approx 11,000 rows and I would like to generate a unique 9 digit number for each line.
I know I could just put 100 000 000 and then increase that by one all the way down but I was wondering if there was any formula or code
VBA that autogenerates a unique number when a cell in excel is filled?
View 5 Replies View RelatedI would like to start with a spending value, (SV), that would be the amount of money that I want to spend in a year, not counting taxes. The function (or functions) would then go through some sort of iterative calculation process that would compute the total spending level including taxes, (TS), the federal tax, (FT), and the state tax, (ST).
These values would have the relationship that TS=SV+FT+ST. When the iteration was finished, the federal tax and state tax amounts would be consistent with the taxes that would be due for this level of total spending as determined by the normal tax tables for both federal and state taxes.
I am not concerned that the tax figures be absolutely exact, so a lot of iteration is probably not necessary.
I have a written a small sub to run solver. However, I need to run a secondary sub at each solver iteration. The secondary sub I want to run is called “BypassGoal”.
My code currently looks like:
Sub OperatingPt()
Dim WrkBk As Workbook
Dim WrkSht1 As Worksheet
Set WrkBk = ThisWorkbook
Set WrkSht1 = WrkBk.Worksheets("Engine")
Dim Mode As String
Dim CellTarget As String
Dim CellRange As String
WrkSht1.Activate
WrkSht1. Cells(30, 2).Value = 2
CellTarget = "H14"
CellRange = "B30"
SolverReset
SolverOptions Iterations:=3000, Precision:=0.000001, AssumeNonNeg:=True, Derivatives:=2, StepThru:=True ....................
I need the easiest way to randomize or generate team numbers in a league. Using COL A assign numbers between 1 & 8 without duplicates. Then repeat 5 more times. This has to be done on the spot and has time constraints as the players will be waiting for their team assignments.
EXAMPLE: I have 48 players which will be assigned to 8 teams of 6. I want to randomize the drawing so the same players don't play on the same teams each week. Also to be able to adjust number of teams determined by how many players are present. either 6 , 8, or 10 teams.
I facing a problem to generate request id number. Actually i need to generate id like example "RQ1013-01" where "RQ" is constant word, "1013" is month and year while "-01"is generated number. and every month i want the id number start from -00 back. thus in a month there is only 99 request is available.
View 6 Replies View RelatedI have a spreadsheet that in column A has someones name, in Column B it has a number.
For example
Column A Column B
Bob Smith 1999
I am looking for a macro that will automatically send an email to this address for example test@test.com, when the number in column B reaches 1200.
I need the email to have in the body - "Bob Smith has reached 1200"
I need this macro to run against 20 sheets in my workbook.
I recently showed my supervisor (again) how to double click on a pivot table to generate a worksheet that shows the records that were used to calculate the pivot table information. He's finally impressed and now wants me to perform the following miracle:
Workbook 1 contains multiple pivot tables showing completed project costs. The tables are broken down by various topics such as designer, worktype, project number, and amount of overrun or underrun. I update this report each month with completed projects.
Workbook 2 is a new report recently developed which contains contract modifications for ongoing and completed projects. It is updated weekly but may be updated monthly in the future.
He would like to click on a project number in Workbook 1 and have it generate a report with Data from Workbook 2 showing all the contract modifications that were written for the project. I told him this would probably work better in Access but he gave me "that look" because he does not want to use Access. This will be shared with other members of management who also do not want to use Access.
Monthly I am sent a spreadsheet detailing cost centre approval ranges for our management team. Unfortunately, the data provided shows a low range and high range in two different columns. Is their a formula to automatically generate the values for the missing numbers
For example
columns A
1001
&
columns B 1007
Can excel automatically generate the 1002, 1003, 1004, 1005 & 1006
How can I generate all the combinations a 3 digit number produces? Example 123 can be written as the following:
123
321
213
312
132
231
I THINK this is all the combinations it will produce.
Not come across this issue before where the Code works once then fails to iterate!
In attached file 140405 Loop not working.xlsm, for each cell in C4:C27 on the Inventory Sheet, the Code should:
Look in the Data Input sheet.
Find the Item Number in Col B corresponding to the number in Col A of the Inventory Sheet.
Find the quantity on the Data Input sheet corresponding to that number.
Copy the quantity into Col H of the Inventory sheet.
The Code works for the first row then throws error message "Run Time error 13" Type Mismatch!
VB:
Sub OFFLOAD()
Dim Dest As Range, Dest1 As Range, oCell As Range
Set Dest = Sheet2.Range("C:C")
Set Dest1 = Sheet2.Range("B:B")
Sheets("INVENTORY").Select
[Code] .....
I am having some trouble with some code I am trying to write for some calculations. I am trying to make excel iterate through a range of cells in 2 columns and compare the cells in each row of the column to a set different number. If both the numbers in the row pass the test then I add them to a count. I know it is simple but I am really struggling with my basic vba knowledge to correctly iterate through 2 columns.
A
B
5
8
8
4
2
9
9
9
For example say my "cutoffs" are a value greater than 2 for numbers in column A and a value greater than 5 for numbers in column B. The program then should return me a value of 2 for this example.
Option Explicit
Function cutoff(phi_cutoff As Double, sw_cutoff As Long, gross As Double)
Dim count As Double
Dim my_range1 As Range
Dim my_range2 As Range
Dim c1 As Range
Dim c2 As Range
Dim value1 As Double
[Code] ............
I'm trying to set up a system of sorts to track "services"
At the top of my 'new service" page, I need a button, which I then need to have a macro run on.
Sheet 1 is basically where I'd be entering the info, and sheet 2 where it would be stored. The button at the top is called Generate New "service' Number. This then needs to generate a new number on sheet 2 in A2 (then once 2 is populated, generate new in 3, then 4, then 5 etc...
I have a macro on Save Service Button at the bottom, which transposes the data into what would be used as a database to store all the services. That Macro then also needs to only transpose and paste data into the row corresponding with the "service number".
For my assignment, I am supposed to create a worksheet that will generate a random social security number (using the correct social security form of 000-00-0000) There are supposed to be a billion social security possibilities and each should be equally likely to be selected in the worksheet.
View 20 Replies View RelatedI had 4 initial temperature values and after some time i got new temperature values. Now for calculating 2nd iteration i need to use new calculated values and for third iteration values from 2nd iteration is to be used and so on.
How to calculate temperature values directly for 10th iteration without solving each iteration?