I have a number result in a cell (d1) from a calcuting formula elswhere on the spreadsheet.
As the worksheet is constantly calculating the result in d1 keeps increasing and decreasing
I would like a formula in cell f3 that will give the result 1 if the d1 has reached the number 45. If cell d1 has not had the number 45 appear f3 will be 0
I need capture a cumulative estimate as it moves from left to right across the page with each passing month. The estimate is pre-filled in for all the months. My task is to select from October to the current month and sum. Is there a way to do that in VBA?
View 9 Replies View Related I have excel formula which let for different numbers appear in the same cell,
=IF(OR(F7={10,10.5,11,11.512,12.5,13,13.5,14,14.5,15}),5,IF(OR(F7={15.5,16,16.5,17,17.5,18,18.5,19,19.5,20}),11,IF(OR(F7>=20.5),15,"")))
for example we have AS1 cell in which appear first number 15, then it change to 5, then it change to 15, then it change to 11 etc. What i need is to capture and lock just the first shown number (in this ex. it has to be 15) and put it to another cell (for ex. cell AT1) Maybe there is any formula ?
I have a date in cell A1, eg 21/02/2008 being 21st Feb 2008 (yes i'm a pommie/limie)
This shows as a date in excel, but if I format the cell as a number, excel will display 39499
So Far, So Good.
Now, I wish the date format to remain in excel, but I wish my script to capture the number as 39499
Unfortunately it captures it as "21/02/2008".
How can I convert the captured value of "21/02/2008" to my desired value of "39499"
And be correct for any date encountered (within plus or minus 10 years only)
My data comes from 3 different sources
Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)
Is there a slick way to capture values based on the number of columns in that row?
I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red.
In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
View 2 Replies View RelatedBelow shows a example table:
*ABCDEFGHIJKLMNOPQRSTUVWXY11458912131415192122232728293234363741444548492314
The table above has numbers from A1:Y1. The blue number is the target number, which is selected in cell H3. I want selected numbers from the target number H1 going along each 3 numbers on the right to get the number 22 which is after the 3rd distance. For example the number would be 22, 29, 37 and 48, which are all in red. After the number 48 it should start counting from the beginning and reach to the target point H1. In this way it will select 5 and 13 also. In total it would be 6 numbers selected. The target point can be selected from A1:Y1 but in this example case is H1.
The table below shows the final 6 numbers, which are obtained after the results along each 3rd distance numbers.
*AAABACADAEAF1222937485132
I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.
A = Departments
B = Calls Dropped
C = Calls Received
D = Current % (C/F)
E = Target %
F = Total (B + C)
G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)
Departments
Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1
21083.33%95.00%12 Dept 2
067100.00%90.00%67 Total
27791.67%95.00%79
I need to get right formula for below details:
Promoters
Neutral
Detractor
no.of Surveys
Percentage
Target
Promoter
needed >75%
Smith, John
5
2
1
8
50.0%
75%
???
Formula to get Percentage: =(Promoter-Detractor)/Neutral
Need to know how many numbers of Promoters needed to get 75.00%
I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.
Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points
Grade are evaluate by:
<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent
Total points / total orders = final grade (or percentage)
As of now i got : 53800 (points) / 624 (orders) = 86.22(%)
Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.
Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.
Amazon seller rating calculator.xlsx
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies View RelatedI am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.
View 2 Replies View RelatedI have a workbook where data is constantly changing
If two cell values become equal I want to capture and keep the value that was in another constantly changing cell at that time
The IF argument will only caapture the moment and then return to the IF False command
I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.
I have streaming data coming into my excel spreadsheet. The data is in 1 cell and its a number that changes almost every second. How can I capture and store this data.
View 1 Replies View Relatedi have a long text in A1 field and i just need to capture some characters in between. Below is the example.
Raw Data:
Cell A1 (r1,c1) = Target: ABC, CustomerOrder, Results: BDE, LastUpdate: 12Dec08
I want to get the Result as below:
Cell A2 = Target: ABC Cell B2 = Results: BDE
Can this be done in Excel Cell format? Or do i need to do it in MS Access?
I am preparing a template on the con-call done with various states. What I want to do is to capture their log in time to the call.
1) As soon as participants log in, we need to input the time of that moment. Click or double click with the cell should give me the current time.
Then;
2) Need to calculate how many participants, logged in to the call On-time by considering the log in time captured.
3) Similarly, we need to count the participants joined within 10 minutes of call and who are late comers.
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies View RelatedI want to extract just the last name from a cell that contains the full name and put just the last into a seperate cell.
WHAT I AM DOING: i copy the contents of a internal screen onto a spreadsheet that i use to check various peices of info and calculations. when i save I like to save my sheet with the last name and then acct number. The internal system screen combines the customers names into a single cell. Right now i have to type the last name into a new cell and have a simple save macro that concatenates that last name with the acct number and saves it into the appropriate folder.
The field i am pulling from is always formatted with FIRST NAME then MI (IF PROVIDED) and then LAST NAME. So when i dump the screen contents into excel A20 may be MIKE SMITH, or MIKE T SMITH. so i need something that looks backwards in the cell and stops at the first space and dumps SMITH into another cell of my choosing (B1 in this case)
In my sheet, i have a list of machines in cells A2 to A5 & list of Plants in cells B2 to B5 as below
Col A..........Col B
Mach.........Plnt
A...............1
B...............2
C...............3
D...............4
What i want to capture is the last machine selected by clicking and also the value of the last plant selected by clicking
I want the machine last selected (clicked) to be in captured in cell D2 & the Plant last selected (clicked) to be in captured in cell D3.
i.e if i click Machine C in column A then go the column B and click Plant 4
Then D2 cell should show C and D3 should show 4
I would like an input box that asks users a select a range of cells....After this certain range of cells is selected....I would than like that range to be copied to a different sheet.
View 9 Replies View RelatedI have a cell (for example, "A1") which is inserted with a WINROS formula to retrieve some data into my spreadsheet. And it is running live at all time.
Actually, I can't think of any formulas to capture the value from cell "A1" to "B1". Because I do not want the value that I captured into cell "B1" running live. I just want the value "B1" fixed after captured.
I have a userform with a 3 refedit controls. Each needs to capture a range from a different workbook (already open - only not active).
Is it possible to configure the refedit to capture a range from another workbook (either thru window menu or ALT+TAB)?
I notice that InputBox type:=8 allows it - I'm a little suprised that default refedit doesn't.
I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case.
Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired.
Sub test2()
Dim xx As Variant
Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value
MsgBox xx
If rng Is Nothing Then xx = 0
MsgBox xx
End Sub
How to modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet.
Refer to the below post: [URL] ....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then Cells(Target.Row, "B") = Now()
End Sub
I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38).
What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value).
I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.
I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10.
I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO).
I tried using a SUMPRODUCT but my formula didn't work.
Is there another formula that will give me this calculation?
I am looking for a formula to capture the "date" of my first sale to new customers.
A B C D E F G H Date of first sale
1 Co. Name 2/2/2012 6/5/2012 7/6/2012 10/16/2012 1/22/2013 4/17/2013
2 ABC Co. $10 $11 $5 $12 $12 2/2/2012
3 Smith Co. $19 1/22/2013
4 Brown Co. $11 $15 6/5/2012
5 AAA Co. $5 $10 7/6/2012
I'm trying to capture a set of forecast dates in a spreadsheet. We have column 1 = forecast date, column 2 = actual date.
I also have at the top of the sheet (which I will hide when I get the forumula to work), today's date, today's date less 13 days and today's date - 27 days.
In column 3, I want a traffic light system whereby if the date in column 2 is equal to or less than (before) each of the 3 hidden dates it the cell colour will turn green, amber then red..