Method For Aligning Multiple Conditions With A Predetermined Outcome
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
ADVERTISEMENT
Apr 28, 2014
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
=SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))
View 4 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
Feb 27, 2013
I have a spreadsheet with an example attached. There is data in multiple rows across multiple columns but not all rows and columns have info in every cell. I would like all the information to align to the far left column but stay in their respective columns.
View 7 Replies
View Related
Mar 17, 2012
I have 35 text boxes to add to the bottom of a chart. Is there no way to mark multiple boxes and align them to the left or right or make them all the same height?
View 2 Replies
View Related
Aug 17, 2007
I have a listbox to select 1 of 15 items. This selection will allow me to access the proper portion of my data. I want to use this number to concatenate the result * predetermined number to access a cell.
Ex
If result from listbox is
if 1 then I want to access data from cell AA7
if 2 then I want to access data from cell AA99
etc.
I can create the cell I want using the code
="AA"&Y2
but what function do I use to call for the cell?
View 9 Replies
View Related
Aug 4, 2014
I need to create a function that adds a predetermined amount (lets say 120) to a cell (lets say A1) every time another cell (lets say B1) reaches a 10 (like 10,20,30,40 etc)
Lets say the B1 is 30, i need A1 to recognize that it needs to show 360 as its value (adding 120 for every 10 of B1)
View 2 Replies
View Related
Mar 23, 2012
I have 800 rows of patient information, I need to sample 70 patients. I used the Random.org randomizer to get my 70 patients. That randomizer only gives me the line numbers such as 1, 8, 23, 45, 70, etc. I then need to go to Excel and just pick out the patients on those lines I was given above. Is there an easy way in Excel to have my spreadsheet of the 800 rows and create a (simple) calculation that will pick out the line numbers that I need all at once, so I do not have to go thru manually and choose the data from row 1, 8, 23, 45 etc to make my new spreadsheet with only my sample patients on it. OR is there a easy way to pick 70 random lines from a spreasheet..
View 1 Replies
View Related
Jan 30, 2014
Cell A1: can contain any number between 1 and 4 (with two decimal places, i.e. 2.36 or 3.99)
Cell A2: based on what number is in A1, this field should return corresponding percentage (%), based on the following criteria:
1: is 100%
2.99: 1%
3 (including) and less (all the way to 4): 0%
Note: percentages between values 1 and 2.99 are equally spread
So let's say, when:
A1 is 1.00, then A2 should return 100%
A1 is 3.26, then A2 should return 0%
A1: is 1.25, then A2 should return 92% (or whatever percentage is correct based on above described criteria)
View 3 Replies
View Related
May 15, 2007
I am using a macro that automatically updates data obtained through a DDE link at 5 second intervals. My code is based off of the article "Automatically Run Macros at a Set Time or Date" found on the Ozgrid FAQs found here: http://www.ozgrid.com/Excel/run-macro-on-time.htm
However, I have this macro execute when I activate a Form Button, not when the workbook opens. So far, this macro works perfectly, and I have no problems. Although, I'd also like to include a Form button to STOP the macro from automatically running if the user chose to do so. How exactly would I go about making this STOP button?
View 4 Replies
View Related
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
View Related
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 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
Jan 13, 2009
I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,
Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.
View 10 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
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
Aug 21, 2009
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View 4 Replies
View Related
Oct 16, 2009
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
View 13 Replies
View Related
Dec 10, 2008
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
View 3 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
Apr 18, 2008
I need to establish a method of interleating multiple dropdowns or Form Control boxes. The purpose is to select one item and have a selection of multiple items associated with the selected item. Example:
Computer training dropdown box 1 offers selections of word, excel, powerpoint, ... If selection is Word, then dropdown box#2 offers advanced in room #15, Intermediate in room #16, Beginning in room #17. If Box 1 selection is excel box 2 selections would be different.
View 2 Replies
View Related
Jan 8, 2010
I have tried to go around the long way to achieve this but came up with pages of pointless code .... I know there is a better way I just dont know enough about VB to do it myself ... And I know this is EASY for many :-)
--------
Cell ranges h11 to as11 are a totals row.
If the total is 0, colorindex is set to vbpatternnone, if >= 1, then colorindex is set to vbpatterngray. Easy right ? I just dont kn ow how to do FROM/IF/DO range loops...
--------
Details:
The code in worksheet_SelectionChange will contain the following:
1: From range h11 to as11, variable1 = application.interior.colorindex of the cell.
2: Check if the cell is >=1 or <=0 ....
3: If >=1 then set application.interior.colorindex = vbpatterngray. Go to #5.
' (This inserts a pattern over the original color of the cell)
4: If <=0 then set application.interior.colorindex = vbpatternNONE
ALSO set application.interior.colorindex = variable1
' (This clears the cell pattern and returns it to original color)
5. Repeat steps to clear cell pattern and restore color / or insert pattern for all cells from range H11:AS11
6. End sub
View 4 Replies
View Related
Feb 22, 2007
How do I sort out columns aligning them to match data in another column?
Column B is 200 rows long all with data such as FLEZ054246. Columns C is 100 rows long but it will match some from B. I need to align C,D,E with B as long as C & B match. The rows that don't match can be left open for C, D, E.
EX.
Column B Column C Column D Column E
FLEZ054246..........TXEZ061244.........WCG................TX
TXEZ061411.........TXEZ059129..........DOUGLAS...........FL[code].....
View 8 Replies
View Related
Apr 30, 2013
aligning some data. Cupno has more entries than seqno and there are duplicate entries. I cannot think of a way to get seqno+results to align with cups. If it's possible to delete rows the don't have a corresponding seqno thats ok too. I've attached the example workbook. Sheet1 is the data and Sheet2 contains what ideally the results should look like
View 2 Replies
View Related
Mar 20, 2014
My application is in vba and I want to log information in text file which required four columns. The tool should open the log file and log the issue and close the file immediately so that other processes running in parallel could also post the issues to it without loss of data. Keep appending to the log file.
Log file should look like: LogFile.txt
View 9 Replies
View Related
