I'm currently using a formula which is exactly what i want but i need it to do something additional.
The formula is: =IF(ISNUMBER(SEARCH("Level 1 ",$I:$I)),$K:$K,"")
My understanding of this formula is that the cell the formula is in then searches column I for the value "Level 1 ", if it's true it returns what is in column K, if false then it returns the cell blank/0. This is a great function and works well.
What i want to know, is there a way to add another rule on to it that instead of returning the value blank/0, it finds the next cell in the column that has a TRUE value and omits it instead.
For example:
At the moment it shows:
Level 1A
Level 1B
Level 1C
(Blank Cell)
Level 1D
(Blank Cell)
Level 1E
But i want it to show like this:
Level 1A
Level 1B
Level 1C
Level 1D (Replaces blank cell with the next TRUE value)
Level 1E (Replaces cell that previously had "Level 1D" in it to the next TRUE value seeing as "Level 1D" is now above)
I don't want the cell to appear as blank.
In column K, i would only have the data for "Level 1 ", i can then copy the formula to other columns for "Level 2 " and so on. I don't need it to return the cells in alphabetical order, just the next true value in the column that matches the values in the reference column.
I'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
I am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333 2 Sam 22 3 Sam 22 4 John 5 5 Sam 22 6 Sam 1 7 Tom 3 8 Tom 333
I am trying to compare two columns (store names) to verify if a store purchased a certain product. column a is all our stores and column c are the stores that got the product. the formula i am using is: =OR(EXACT(A2,C2:C93)) in column B and it returns a true or false value. However, when I copy the formula down column B, not only does the A value change, but so do the C values. How do I keep the c2:c93 portion of the function the changing, while the a2 goes to a3, a4, a5 etc.?
In the first, C3, the full date is entered in format dd/mmm/yy. In the second, C5, the month is entered in full, say "November".
I want in C7, to have a formula that tells me when the month part of C3 is the same as either the month in C5, or any of the other quarter ends starting from that month.
So for example, if November is in C5, then my four months would be November, February, May, and August. If the month part of C3 (=MONTH(C3)) is equal to any of these months. I want to have to enter only one of the four months though!
I want to lookup a value using * and ? and have the result tell me whether all possible values have a "yes" next to them.
Let me explain. Example.
If I have this in table1: Records Yes/No adfcjks7as Yes 91521cg N/A 215a786 N/A 9194215g Yes 2154a6 N/A 614asdff No asdf562 Yes as895as Yes a5s5d8d6 No
And I type *as in cell A1 THEN I want cell A2 to lookup "*as" in Table1[Records] and determine if all possible records have "Yes" next to them in column Yes/No. The result is "ALLYES" if its true or "NOTALLYES" if its false.
In this example the result is "ALLYES" because adfcjks7as and as895as are the only records that *as applies to and "Yes" is next to them in the same row. If there is only one possible value then it still applies.
Of course * and ? will be used in different arrangements to find possible values.
I've collected some GPS data of an athlete running and want to quantify both the number and duration of individual sprints.
In the attached xls file, there is a column for time, speed and another that returns whether the speed column is >18 kph or not. False is <18, sprint if >18 kph.
Each entry with a "sprint" means they are sprinting (running above 18 kph) and each entry is 0.2-s. Thus 5 entries added together = 1-s.
I want to know, not the total number of sprint entries but the number of sprint groupings for example.
false, false, sprint, sprint, sprint, false, false,false,false, sprint, sprint, false = two seperate groups of "sprints". I would therefore conclude that the athlete sprinted twice.
I also want to know the duration of the sprint for each of the groupings. For example based upon each entry being 0.2-s, the first sprint grouping would total 0.6-s and the second grouping 0.4-s.
I have two groups of numbers. The first group is x amount of rows and each row has 6 numbers. The second group is x amount of rows and each row is 7 numbers.
Each number from the first group needs to check to see if it exists in the second group. If it does exist, the cell should change to a highlighted cell, or switch to an 'X' or something else that identifies it has been matched. Note: Numbers may be duplicated in both groups. Is there an easy way to do this, or will it involve programming? I'm attaching an example of the "group" setup.
I am trying to do is develop a electronic visitors log in excel that has three statements visitors must read and accept before they can enter. I have a userform with date/time, name etc, then the three buttons that open another user form with the statements for them to read and 2 command buttons (Accept and Decline), Accept takes them back to the log-in userform and Decline closes all forms.
The problem I am having is I can’t get the checkbox on the log-in form next to each statement to go true when the accept button is clicked for said statement. I would like to disable the checkbox on the visitorsignin form so the only way it goes true is from the statement form.
I hope I explained myself well enough for someone to understand what I’m trying to do, the code is not finished yet either, still waiting for signature pad to come in so I can paly with it and figure out how to incorporate it into the file.
how to denote a cell as blank? I'm trying to do an IF formula for if the cell is blank.. Actually it would also work if the formula reads if the cell was NOT blank. Either one would work I just can't figure out the name of an empty cell. So I'm looking for this with the right word in the formula. =if(A1="blankcell",true,false)
I want to perform a SUMIF if more than 1 condition is true. I can do a regular SUMIF if, for example, values in column A is > than 10,000, then SUM associated values in column F.
However, what if I want a SUMIF if values in column A is > 10,000, values in column B = “Yankees”, then SUM associated values in column F.
I have one tiny qs which I hope someone can answer for me.
I need to allow a user to see a dropdown list (with options) only when the previous cell has the word "complete".
For example, cell B1 can have: On going Complete Blank
If B1 has complete then I want the user to be able to select a number from a drop down list in C1 otherwise I want it to say "wait" or if its Blank to be [Blank].
I tried the Validation > Custom and put in =IF(B1"Complete","Wait",OptionsList)
Where OptionsList is a defined list of 5->1 but this lot didnt work. I tried similar things in conditional formating but nothing again.
when you type True into a General formatted Excel cell Excel returns TRUE. This value cannot be formatted to a 1 (or a -1) by applying a different format. Thus, does Excel have a boolean data type? Or is it something else like a numeric value that for whatever reason is not receptive to format changes.
Sub Macro3() ' ' Macro3 Macro ' Macro recorded 8/16/2006 by BPETTIS '
' With ActiveSheet. PivotTables("PivotTable1").PivotFields("CT Comp") .PivotItems("0.9").Visible = True .PivotItems("1.3").Visible = True .PivotItems("1.4").Visible = True .PivotItems("1.5").Visible = True .PivotItems("1.6").Visible = True .PivotItems("1.7").Visible = True .PivotItems("1.8").Visible = True .PivotItems("1.9").Visible = True . It was recorded when in the indcated PivotField is selected the "ShowAll" option.
However, when I try to run this macro I get the following error
"Run-time error '1004': Unable to set the Visible property of the PivotItem class
Why am I not able to execute a recorded macro. Also how do I set a Pivot Item's visiblity to "TRUE"