In A1 have two words, say "first" and "second" as two lines entered by pressing Alt+Enter after typing the first word. The requirement is remove this formating OR to get these two words separately in single cells. ie B1=First & C1=second.
View 3 Replies=COUNTIFS($K$54:$K$84,">25
View 9 Replies View RelatedI'm trying to calculate the sum of rank vlaues in an array formula (required for a Mann-Whitney U-test calc). For example, I have the results of a survey quesiton (1-5 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties.
The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below.
$C$% = "Sales" and $F$5 = "Marketing"...
={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}
I have this Formula in a excel sheet =IF(AND(COUNT(F12;G12)=2;G12<F12);G12-F12;IF(AND(COUNT(F12;G12)=2;G12>F12);ABS(F12-G12);""))
My problem is that when the result is 0 it just leaves a blank cell without a 0.
Can i correct this so that when the result is 0 it will actually show a 0 in the cell? I can´t put a 0 in the end of the formula because then it shows zeroes in all the cells without information aswell.
I have a userform which displays data in a sequence for my workbook.
For now it is performing almost the way i wish, the data is displayed as the code loops thruogh a range (K6:N2000) when the condition is met.
Private Sub CommandButton1_Click()
For r = 6 To 2000
For c = 11 To 14
Application.ScreenUpdating = False
If Cells(r, c).Offset(1, 0) Cells(r, c) Then
Cells(r, c).Select
Dim sWhat As String
Dim wWhat As String
sWhat = ActiveCell.End(xlUp).Value
MsgBox "Generate " & sWhat & " " & ActiveCell.Value & "'s" & " requirement data.", _
vbMsgBoxSetForeground, ("Azone requirment")..................
I am preparing a daily report available in sheet1 with the actual input in sheet2 generated through a tool.
The report contain three columns i.e. total cases, Total correction done and Not corrected. From the total cases on a specific date, I need to calculate how many correction have been done and how many are pending.
E.g. 01 January 2013=5 cases are uploaded in the tool.
on 02 January 2013, it was observed that 3 cases were corrected and 2 are pending But 9 cases cases are uploaded on the same day in the tool .
So Total correction done (C3)=3
Not corrected(D3)= 11
I have different dates in column A, let's say:
10/26/12
11/1/12
11/15/12
2/12/13
I want to say that if the date is <= 12/31/12, put "LEASED IN 12", othwerwise put "LEASED IN 13".
My formula:
=IF($A1<="12/31/12",'LEASED IN 12","LEASED IN 13")
I get only 'LEASED IN 12".
It show error, how to correct it .
[Code] ......
Sub startrd3_click()
Range("B5:AY64").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Key2:=Range("C5") _
, Order2:=xlDescending, Key3:=Range("E5"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' ERROR CHECKING HAPPENS HERE TO MAKE SURE PEOPLE HAVE NOT PLAYED EACH OTHER BEFORE
Columns("J:W").Select
Selection.EntireColumn.Hidden = True
Range("I4").Select
End Sub
If any of you gamers out there are familiar with the Swiss Pairing System, the goal of this sheet is to perform the pairing functions based on results. The goal of swiss pairing is to partner folks up based on win loss records, however, the players can never play each othe more than once. What i am wondering is how to create the vb code required to make sure the pairings are unique while keeping the rules of swiss pairing together.
Here's how it works:.............
I am trying to enter the last name "Teh" in a cell. How do I turn off auto spell correction that turns "Teh" into "The"?
View 3 Replies View RelatedI need to step out of a macro based on a user respone, and I don't know how to end this macro if the user needs to correct a cell entry.
If they respond "no", I want to sub to end and select the cell where the correction needs to be made.
I need a hand with a worksheet that we use.
We do a penetration test on soil, write down the values from the machine, then enter into excel and it plots a line graph.
Depending on the values, sometimes a correction is required. This is manually done at the moment.
I would like to have excel do it for us.
I have attached an example of a manual correction I have done as well as the excel calculation worksheet.
There are 2 results given (one at 2.5mm penetration & one at 5.0mm penetration), we calculate both, then use the highest result for the report.
the x value is a constant and the y is a variable.
Attached Files :
Copy of Master WA CBR Worksheet Soaked.xls‎
DOC230714-002.pdf‎
I have the following VBA code and try to sum the range from B2 to CY2 for DA2; then B3 to CY3 for DA3, etc. Therefore I use the for loop. It results in "Application defined or Object defined error".
View 4 Replies View RelatedI have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View RelatedI am using the formula =IFERROR((D10*0.5)+(E10*0.5),"") but in some field it gives me a percentage that rounds and others is giveing me a 4 digit percentage
View 3 Replies View RelatedSo I am setting up a spread sheet that tracks time users play on a video game. I can check this info online for each users and the site has the play time in d:hh:mm:ss format. So now I have entered the info for the players and I need to create a formula to show how much time was played that week. Example
Cell B2 is time on 2/27 and Cell C2 is time on 3/6 in Cell D2 I need to show time played in between those dates.
B2 = 0:21:27:00
C2 = 1:01:14:00
D2 = ?????
Note: I have the cells Format as Custom and Type is d:hh:mm:ss
I have a question that I've been struggling with for a few days now. I work with excel quite a bit so I'm used to setting up complex formulas, however I have something that has stumped me.
I have one cell setup with the following format for a phone number: (000) 000-0000. Now what I wish to do is mistake proof the cell and receive an error if it doesn't contain 10 digits.
If I type: =if(A1= (000) 000-0000, A1, "Incorrect") Then it will return as False because it's searching for that exact syntax. What I want it to do is have the cell recognize whether or not it contains 10 digits, so that if I mess up and have 9, it will easily be spotted.
Is this even possible?
I have a workbook containing 5 sheets. Sheet1 is my results page and the balance contains employee names and dates training completed(matrix)
On sheet1 I have 4 columns - 1name, 2type of training, 3date completed & 4refresher due. I have created a combobox and used the vlookup formula to populate the cells within the third column but I seem to have a problem with the date format. In the 4th column I have used this formula (=c2+365) this displays a new date one year on, but if an individual hasn't completed training and the cell within c2 is blank it defaults to this - 30/12/1902. If cell c2 is blank I would like d2 to be blank too.
I have a file with two columns
Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.
Column B has serial numbers of equipment from column A.
I need to create a complex conditional formatting, which will check:
If A1=EQ1, and B1"HCC*" then make it red,
OR
if A1=EQ2, and B1"ABC*" then make it red,
OR
if A1=EQ3, and B1OR("CDE*","FGE") then make it red.
And then same thing for A2, B2 cells.
I have thousands of rows in both columns.
I have to check whether selected equipment name corresponds with entered serial name pattern. I don't know how to do it for each row separately.
I thought of something like:
1st condition: =AND(A1="EQ1",LEFT(B1,3)"HCC")
2nd condition: =AND(A1="EQ2",LEFT(B1,3)"ABC")
...
But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?
In cell A1 is time now. =NOW()
Cell A2 is a date in the future.
I want to calculate remaining time in Months/days/hours/minutes format by subtracting A1 from A2.
******** ******************** ************************************************************************>Microsoft Excel - time left.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD125/09/2008 16:49 212/10/2008 07:30 3? 4Result in A3 to be in months, days, hours and minutes format Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank.
We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.
I am trying to format cells in a range that is not a formula. I tried a UDF and CF but would not work properly. Maybe due to a Bug in Excel.
Therefore created the code below:
I put this code in my ThisWorkbook Module.
What I am trying to accomplish is this.
Check the range of cells, if any cell is not a formula color the background Yellow and the foreground Bold RED.
The workbook has about 40 sheets 32 of them are formatted the same they are numbered "01-31" and 1 is "Temp".
This code works fine but the second procedure would not work when this 1 is there.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myCell As Range
Dim myIntersect As Range
Dim myAddresses As String
Dim SheetNamessToSkip As Variant
Dim res As Variant
SheetNamessToSkip = Array("Splash", "Menu", "Diesel", "Gasolina", "Alcool", "Prev Month", "Totals")
myAddresses = "G12:H51,P40:Q44,P48:Q60,Q14:Q17"
Both subs do what I expect them to do, but can not run them together.
I get a compile error when trying to do it this way.
How can I underline the formula in a text like so:
My name is Powder
from this formula
="My name is "&D29
I am trying to limit the format of data to all cells in a column to 2 letters followed by 6 digits. The letters and the numbers are not required to be the same, but the person entering can only enter in that format.
View 2 Replies View RelatedI am using the VLOOKUP function in a cell, however, I would like the value returned to also retain the text format from the table (eg colour and bold). I have attached a simple example (to demonstrate the point) where cell B14 should have the word 'French' in bold red.Example.xlsx
View 3 Replies View RelatedWhat would be the correct formula to check the format of an address that must start with a number followed by a space then text - in cell B4
At the moment I have:
=IF(B4="","r",IF(ISERROR(SEARCH(" ",B4,1)),"r",IF(ISNUMBER(VALUE(LEFT(C5,SEARCH(" ",C5,1)-1))),"a","r")))
If it is incorrect or missing an 'r' should be displayed, an 'a' if correct
I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.
At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.
I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.
The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.
My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
I have a set of data in A:P
I have a named range "TR" in which I need to look up a value
I would like to conditionally format Columns M:P if the value in the cell is less than a value in the lookup table
Column M corresponds with the 2nd column of the lookup table
Column N corresponds with the 3rd column of the lookup table ETC
My formula would be:
if M2 < Vlookup(b2&g2,TR,2,false) then conditional format
if N2 < Vlookup(b2&g2,TR,3,false) then conditional format
etc for Columns O and P
I have attached a sample workbook. TEST CONDITIONAL FORMAT.xlsx
how to change a proportion of text in a resulting concatenation
formula.
cell A1 contains the text in bold "ABCDEF", cell A2 containes "GHIJKLM" cell
A3 contains =A1 & " " & A2 but I want the cell A1 part to remain in bold. Is
there a way of using the TEXT function e.g. =TEXT(A1,BOLD) & " " & A2?