Outcome If Variables Sum Certain Percentage?
Nov 11, 2013
See the dataset below.
If the total # of items is less than 61, then I want to get a "2" if the difference between the errors and # of items is greater than 5% and a "0" if it is less than 5%.
If the total # of items is greater than or equal to 61, then I want to get a 2 if the difference between the errors and # of items is greater than 1% and a "0" if it is less than 1%.
Jan
Feb
Mar
Total
Errors
1
0
0
1
Total Items
50
50
50
150
>= 61 total items, Target is 1% error Rate. The above table has an error rate of .66% (1/150) and should be scored "0".
View 1 Replies
ADVERTISEMENT
May 15, 2014
On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:
Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)
The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%
Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%
Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%
Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%
View 1 Replies
View Related
Mar 7, 2014
I have the following columns:
Status (column C), New Status (Column D) and Date Sent (column M)
Status column (C) contains multiple stages of progress status (Being Reviewed, Filed Not, etc)
New Status (D) column is blank (this is where I will enter the formula)
Date Sent (M) has mostly blank cells but some with dates in them
I would like a formula in column D (New Status) which evaluates C (Status).
If C = "Filed Not" AND M is blank, then output "Filed Not - Not Sent"
If C = "Filed Not" AND M has a date in it, then output "Filed Not - Sent"
If C <>"Filed Not" then output whatever data exists in C
I can get each of these to work individually, however, I can't, for the life of me, get them to work into one equation.
View 7 Replies
View Related
Jun 5, 2014
Currently I am using an index matching function as shown below as I am looking up based on multiple (2) criteria. However for some instances I know that there will be 2 or even 3 possible outcomes that this formula could return. Despite the fact that each of these values is exactly the same, the function returns an error, which I am assuming is because it found multiple solutions.
View 4 Replies
View Related
Jun 24, 2009
Is there a way of making a SUM formula dynamic using the outcome of another cell.
Say of the outcome of a cell is 2 it would count down 2 rows so the sum would read
View 6 Replies
View Related
Jul 25, 2006
I have a macro implemented in my worksheet which plots graphs. When you press it, it plots a graph.
What i would like it to also do is:
(a) create a tab name which states Graph_1.45 {last bit is whatever the name of the data worksheet is}. - so for example i will eventually have 20 worksheets of data of the same form as the attachment. Each will have the option of the graph. So each will haveits own name, by pressing the graph button the name thatis given is Graph_"whatever the tab sheet of data's name is".
(b) Now the problem is if a user forgets to delete the worksheet it comes up with an error. Is there a way by which if the graph already exists the macro comes up with a message "Graph already exists" and gives an option to delete that graph and plot nbew one?
View 9 Replies
View Related
Mar 4, 2009
I am wanting to create a VB script that will take a number (in general format) of two or three digits, convert it to text, then make sure that there are enough zeros preceeding the number to make exactly 6 digits. Examples of the initial number (before the macro is run) and the final number (after it is run): ..
View 8 Replies
View Related
Apr 10, 2012
If the percent in a cell is more than 10% less than another cell I would like it to turn red. I would like the cell to turn yellow if the percent in that cell is within 10% below the percent in another cell, or if the percent is greater than that of another cell I would like the cell to turn green. How do I do this?
To clarify; If cell a1 is 20% and Cell a2 is 21%, I would like the color in cell a1 to turn yellow since it is less than 10% below cell a2. OR if cell a1 is 25% I would like it to turn green since it is greater than cell a2. OR if cell a1 is 10% I would like it to turn red since it is more than 10% below cell a2.
I've looked at conditional formatting and it does not seem to apply, this seems like it would need an if function combined with conditional formatting.
View 3 Replies
View Related
Dec 20, 2012
An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.
I would like results for my request of 50 matches to be displayed as follows.
HHH
HHD
HHA
HDH
HDD
HDA
[Code] ...........
Where:
H=HOME
D=DRAW
A=AWAY
Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.
View 9 Replies
View Related
Jul 6, 2009
i am looking for a formula, if i put it as plainly as i can then i hope someone can work it out for me!
if ad4 is greater or less than 30 then ad4 text is red, if ad4 is equal too 30 then text is green
View 14 Replies
View Related
Dec 8, 2009
See example attached. I am building a form where a user need to provide different criteria to receive a specific hourly rate. Conditions are:-
1. Age
2. A / B / C / D
3. Weekend (W) or Mid Week (M)
There are 4 age groups, 4 categories (A,B etc) and 2 Daily rates, therefore there are 32 different possible rates. I have considered nested if statements but I think this would be a very long formula - is this the only answer?
View 4 Replies
View Related
Jul 17, 2012
I breed snakes as a hobby and have around 30 females and 15 males Different morphs of Royal Python
There are a few things i am trying to do and will start of by asking the most simple one
If i have a column of females and a column of males can a formula be made to show all the possible pairings between male and female?
View 8 Replies
View Related
May 29, 2014
I need to fill 2 other cells with varying colours dependant on what is chosen within another cell.
I have a drop-down list in cell A2 to select either ON SITE, ACTIVE or CANCEL.
What I need then basically is...
if A2 equals 'ON SITE' then fill other two cells (A3 and B1) GREEN
if A2 equals 'ACTIVE' then fill other two cells (A3 and B1) YELLOW
if A2 equals 'CANCEL' then fill other two cells (A3 and B1) RED
[File attached]
View 8 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
Jul 1, 2008
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
View 2 Replies
View Related
Apr 3, 2013
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
View 2 Replies
View Related
Apr 26, 2013
calculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%
View 3 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
Apr 27, 2006
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
View 3 Replies
View Related
Jan 16, 2007
i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".
At moment I use following
vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0
vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0
until...............................
View 3 Replies
View Related
May 26, 2014
How can I toggle between percentage and actual value to display on a pie chart?
View 1 Replies
View Related
Apr 8, 2014
I have a whole number (207) in B5. This should be seen as a percentage (207%).
I want to add this percentage to the value in M15, which is not a percent (2705.8).
I would like to display this sum in M17.
The number that is displaying now, after simply putting 207% (I don't want the % symbol in there) in B5, and the formula =sum(M17,B5) in M17 is 270787%.
This is obviously not correct.
View 3 Replies
View Related
May 5, 2014
Why is Excel not rounding up a percentage of 93.5 to 94?
View 6 Replies
View Related
Jun 21, 2014
I have a simple issue i cannot figure out how to write a formula for.
In A2 i have the number of operations.
In A4 i have the percentage of CPU usage it requires to complete those operations.
I need an output somewhere that will tell me how may operations I can get per 0.1% of CPU usage.
View 5 Replies
View Related
Dec 16, 2013
The cell I am referencing value is 100,000
I need to take two percentages off of that number. First 50%, then 37% and have the value in a SINGLE cell.
Because 100,000/1.87 is not the same thing....of course.
View 11 Replies
View Related
Mar 6, 2008
Is there a formula that I may use to keep track of the YTD percentage of attendance of volunteer workers over the course of a year? I have approx 200 workers meeting once a week. Their names are listed in column "A" from A-4 to A-204. Starting in column "E" is the date of the weekday for attendance.
For Example: E-1 is dated Jan. 7, 2008. F-1 is dated Jan. 14, 08 etc. I place a one (1) in the corresponding cell to show that they attended. The formula I need would start in cell D-4 and be dragged down to D-204.
There also must be a way to factor in the weeks which we are to meet that must be cancelled due to weather etc. The worker should not be penalized due to our cancellation.
View 11 Replies
View Related
Nov 25, 2008
I was wondering what the formulas was for adding 20 percent to a price of lets say 10.00. What would I use for this?
View 2 Replies
View Related
May 13, 2009
I've seen a few finance excel sheets lately (don't have an example right now... ) that have a total number for something and then in parentheses next to it a percentage of the total is automatically calculated. So I have 5 pens and that's 10% of what I have in my pencil cup --> 5 (10%) is how it is displayed.
View 3 Replies
View Related
Sep 17, 2009
I would like to know whether it's possible to evaluate the percentage of a text value ie 'Approved', after filters on other columns have been done.
AREA YR DECISION
HN 09 APPROVED
NYM 08 REFUSED
RC 09 APPROVED
HN 09
RC 08 REFUSED
? %
So if I filter by AREA and/or YR, I would like the % of how many were 'Approved' in that filtered range (ignoring any blank cells in the DECISION column).
So far I have a basic
=TEXT(COUNTIF(C2:C6,"*Approved*")/ROWS(C2:C6),"0% ""Approval Rate""")
but it doesn't work once I filter by AREA or YR, and doesn't ignore blanks.
View 5 Replies
View Related
Jan 26, 2009
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.
The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
View 9 Replies
View Related
