The values in F4:F14 are changed depending on the value in F2. The rows in green have a fixed value. The row in yellow has a lower limit (170)and upper limit (195). I cannot get a formula in F14 to perform this function.
View 3 RepliesI have a range of numbers:
A1=24
A2=17
A3=9
A4=4
Based on this the MEAN=13.5 and STANDARD DEVIATION= 8.81286937760152
I want to create a formula to calculate the UCL and LCL
When I use MINITAB I get UCL=31.23 & LCL=-4.23
I want to generate random times in excel between upper and lower limits , which i can do , but i also want dates in same cells with out randomization , means dates should act independently and time should randomize
it should be something like that
8/1/2013 05:55 AM
8/2/2013 07:55 AM
8/3/2013 04:44 AM
I have a table that I use to generate about 20 graphs. The lower and upper limits can be manually altered in the table. If the user leave those 2 cells empty, I want the upper and lower limits be determined by default by Excel. So that the limits don't have to be entered for every graph in order for the graphs to be generated without any debugging error.
With ActiveChart.Axes(xlValue)
.MinimumScale = y1Lower
.MaximumScale = y1Upper
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
where
y1Lower = .Cells(i, j + 9)
y1Upper = .Cells(i, j + 10)
Is there a data validation that will not allow all upper case or all lower case characters?
For examples:
Allow:
John Doe
101A N Woodbine Street
Disallow:
JOHN DOE
101A N WOODBINE STREET
Disallow:
john doe
101a n woodbine street
Or perhaps formatting that would convert the words to upper/lower case?
Or, I would be interested in VBA if it is the only way.
I have this code (this is just a snippet)
For Each cel In Range("E6:AI15").Cells
If IsError(cel.Value) Then
Else
Select Case UCase(cel.Value)
Case "H"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 3
Case "S"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 10
but I want the cells to accept both upper and lower case values and format the cells accordingly ie. H or h, S or s
I need to change the all Upper Case character to Lower case character for all cells. See below my code, i am getting error message, Tye Mismatch. "cell.Value = LCase(cell.Value)"
[Code] .....
=upper("venkat") returns VENKAT
=upper(a1) returns the text in A1 in upper case
similary lower
proper will turn the first letter into uppercase
is this what you want;.
Terry <terrybetts11138@hotmail.com> wrote in message
news:dbl0dk$poa$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Win XP Pro
> Office 2003
>
> Using "Excel" and wish to select any text in a worksheet to ALTER the case
> to either upper or lower.
> When using "Word" it is easy via the menu.
> Is there a menu driven option in Excel
of using a function for this purpose.
find the "add in" that converts the case text
from lower to upper in excel. i've already downloaded this before but can't
remember where from and it is much easier than creating a formula or a macro
I am attempting to change a range of cells from lower case text to all upper case text.
View 4 Replies View RelatedI have data in cell A1 as below:
36, TREE ROAD, 5TH FLOOR
I want it in proper format in cell A2 as below:
36, Tree Road, 5th Floor
I used formula =Proper(A1), it gave results as:
36, Tree Road, 5Th Floor
The only problem I face is after any number, the next character should in lowercase, i.e. 5th
I have a workbook on which I want to change all the text from Lower to Upper case.
How can I do this without retyping .
I've got a column of data that I need to split into further columns, the only way of identifying the split from the data is if a lower case character appears next to an upper case character.
For example, a cell in column A could look like;
Computer ProductsDrivesDVDExternal
And I'd need to split that out to cover columns A B and C with
Computer Products / Drives / DVDExternal
I'd need to do that for all the cells in column A.
I am using the Find fuction to look for values in cells that contain text in both lower and upper case. I am comparing the values that I have stored in a lookup table. I am running into trouble when the lookup table doesn't match the text cells....
View 9 Replies View RelatedI want to make a line chart that plots a variable over time (say, monthly) and that shows upper and lower limits as the 2nd and 3rd plot. My table layout, boiled down to the bare essentials:
Col 1 = Measurement date
Col 2 = Measurement (pressure, for example)
Col 3 = High limit (never varies)
Col 4 = Low limit (never varies)
The chart/graph in this case would show at a glance how water pressure varies over time and when it goes over (under) a limit. The table has about 50 variables.
Im looking to use vba to determin upper and lower quartiles, i know how to do this through excel "QUARTILE(B9:B59990,1)", but was wondering if anyone would of any code to do this in visual basic?
View 2 Replies View RelatedI have the macro below, how do I make it NOT be case sensitive, I want it to work for test. Test, TEST
Excel 2003
Code:
Sub test()
If Left(ActiveCell, 4) = ("test") Then
MsgBox "Activecell starts with test"
End If
End Sub
I would like to format a row of cells so that when a word is entered into the cell it automatically becomes a capital.
I need the word to be capitalized so that I can use it in a custom function. The function uses the word from this cell and goes through a bunch of cases in determing how to classify the string.
I think more than one solution is possible and I would greatly appreciate some feed back, I've tried looking into turning all the letters of a string in my VBA code to capitals, or a way to format the cells, so that the string is already capitalized when entered into the VBA code, but I'm still a novice at VBA and unsure on how certain commands work.
here is a sample of my vba code.
Function WeightI(Shape As String, sDim As String, dLenFt As Double) As Double
Const pi As Double = 3.14159265358979
Const Ft2In As Double = 12
Const dDen As Double = 0.2835 ' density of steel, pounds per cubic inch
Dim aiStr() As String ' dimensions as strings
I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.
With . Cells(Lrow, "B")
If Not IsError(.Value) Then
'****
If LCase(.Value) = LCase("D") Then .EntireRow.Delete
create a formula copying a formula from cell a5 to a20 but i want the result to be 1 cell lower from a5's result
View 6 Replies View Relatedif there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
When I use a simple formula such as:
=upper(a1)
that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.
What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet
I'm having trouble with my self designed "program" in excel.
Is there a way to insert a row and copy the value or formula of the upper row? withouy using macro.
I will enter a figure into A1. I will also enter a figure into C1. In E1 I would like to enter the lower of: 0.2% of A1 or 75% of C1
View 5 Replies View RelatedI have a spreadsheet that I am using to track competitor retail pricing
The problem is that I have a list of values and I want to be able to show how many of these values are either higher, lower or the same to a specific cell value. However one complexity that I have is that some of the competitors do not have values for all records (i.e. some are blank) and I want to discount these from the counting.
I am looking for a formula that I can use in the lower table to fill in the missing County cells, based on the values in the Town/Zip columns on the top table (I apoligize for the crudeness):
CountyTownZipAbleTownA00000AbleTownB00001AbleTownC00002BravoTownD00003BravoTownE00004CountyTownZipMemberTownD00003BlackTownC00002FrankTownA00000GreeneTownB00001JonesTownA00000SmithTownC00006ThomasTownE00004White
This would consist of hundreds of Zips and Towns and this is just an abbreviated mock up.
I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?
View 2 Replies View RelatedI work in Insurance and have a spreadsheet which requires [oil] values to be entered into Column J. I then have three additional columns (K,L & M) and I require them to do the following:
K: take the value from J but cap it at $100,000,000.
L: take the value from J but have a minimum value of $100,000,000 and a maximum value of $400,000,000.
M: take the value from J but have a minimum value of $500,000,000 and a maximum value of $100,000,000.
For example, if i were to enter a value of $650,000,000 into Column J, Column K should show $100,000,000, Column L should show $400,000,000 and Column M should show $50,000,000. Likewise if I were enter $15,000,000 into Column J, Column K would show $15,000,000, Column L would show $- and Column M would show $-.
I copied this from Excel:
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
I have an Address Book from an old Personal Information Manager that exports the file as ASCII.
My problem is when I view the .asc file in Excel, Excel has created two rows for several entries.
That creates a problem as I wanted to select Column "A" then do Text to columns with a comma delimiter (the asc files has the comma in place)
However, since Excel put in the second line, I'll not be able to put in a header column.
When I look at the .asc file there is no way that there is more than 32,767 characters in each row. So why did Excel split it up? ....