# Copying Formula To 8 Cells Lower With Result Of 1 Cell Lower?

Sep 30, 2013create 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 Repliescreate 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 RepliesADVERTISEMENT

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 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 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.

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 Replies View RelatedI 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 RelatedIs 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 a huge list of data with numbers in cells. each number also has a corresponding percentage in the next cell over. In some cases, some of the numbers appear twice with differentiating percentages in the cells. What I am trying to do is automatically highlight and delete the duplicate number with the lower of the two percentages. See the below example.

2069091%

2069088%

As per above, I would want to delete the duplicate number with the 88% since it is the lower of the two percentages of that number. I have almost 2000 cells and have been going through manually deleting the lower of the two percentages for the duplicate numbers.

I am inserting a subtotal above my data set, the first cell that this subtotal takes place is "R1", then I have an unknown number of subtotals going right, depending on the number of months FCSTed by my sales managers.

Attached you will find a subset of the data and you will see that I need to select "S1 : iLastColumn(1)".

I 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 would like to compare two cells side by side and populate the higher of the two cells in one color and the lower in another

View 3 Replies View RelatedI 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 am trying to get a simple formula to look in A1 and if it is lower than A2 then return the value in A1 if it is not then return the value in A2..

View 4 Replies View Relatedi wanted to know if there is a way which i can so that would tell excel if a certain cell value is lower then another it would give a msg

for example

Cell A1 - 5

so if cell A2 is less then five and a macro is clicked then the macro would give a warning msg hence ending the macro

I want to have some cells with conditional formatting.

The case here is date.

I have a given range of cells which will take on date. I will select all of these and will creat conditional formatting so that if the date is less than 2 days or more, have font colour change to red.

Now, the current date will be refered on a single cell.

I have done =($C$3-2) and it works but this does not account for anything older than 2 days.

What am I missing here? How do I do or less then?

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.

When I try to copy formulas across do I get a little black plus-sign in the lower right corner of the cell, when I point to it. I can then drag the plus-sign to the the area where I want to copy the formula. This plus-sign has now disappeared.

View 3 Replies View RelatedI 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)

This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):

1) I want user to input starting cell. So I need to create a button for that.

2) This is where the logic begins and I'm having difficulty.

From cell defined in button, I move up one cell in that column and compare that value to the original cell.

If the value is greater than original cell then logic needs 2 outcomes:

a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.

b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.

I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.

I'm very new to excel macros and the parts I think I've put together are:

Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _

xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

xlNext, MatchCase:=True).Activate

[Code].....

if cell = null, then skip it to the lower cell.

the data is like this:

A361

A37

A38

A391

and i want to be like this:

A36

A39

without delete the row.

I have 83,33 value. If I like to increase into 84 and not 83 without decimal point

View 11 Replies View RelatedI 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 have looked in conditional formatting, but do not see a way to change lower case to all caps. Is there a way to do this so that when someone types in a cell it returns all caps instead of leaving it in lower case?

View 9 Replies View RelatedI 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] .....

How to change the lowercase letters to uppercase letters?

While using the userform, for example "name" is entered in the form.

My requirement is the values should reflect as uppercase letters like "NAME"

I am trying to change string values of a name from uppercase to lowercase. The data is layed out as lastname then first name. As an example, I have the following values in column C:

ANDERSON MICHAEL

SMITH SUSAN P

JOHNSON PATRICK

JORDAN MICHAEL R

WOODS TIGER

CLINTON WILLIAM J

I am trying to convert to following (with "," between lastname & firstname):

Anderson, Michael

Smith, Susan P

Johnson, Patrick

Jordan, Michael R

Woods, Tiger

Clinton, William J

I have a start of how I think the code should flow but can't figure out how to do the conversion.

=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 have name and a number in this

format: cell a1 Jones---56. In cell b1 I have another name i.e.,

Johnson---45. How can I subtract 45 from 56, take 60% of the difference

and in cell c3 show the following; Johnson---6. All numbers are to

rounded down to 0. The final result would look like this;

a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells

with the same type of info in them. Example; a2 Smith----34, b2

Jones----78, c3 would show Smith----26.(rounded down) I always want to

subtract the lower number from the highest and take 60% of the

difference.

Copyrights 2005-15 www.BigResource.com, All rights reserved