Forcing Cells To ALWAYS Find MIN And MAXIMUM Values From A Specific Range Of Cells

Feb 1, 2010

I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.

Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".

Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?

View 3 Replies


ADVERTISEMENT

Minimum And Maximum Values Within Specific Date Range

Sep 30, 2009

I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) ....
1/1/09 10 8
2/1/09 8 6
3/1/09 5 2

View 4 Replies View Related

Adjacent Cells - Search For Specific Values And Find The Total Number Of Instances

Jan 30, 2014

Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.

View 14 Replies View Related

Find And Replace Different Values In A Range Of Cells

May 8, 2008

I would like to be able to replace all cell values in a range of 20c by 20r (i.e. 400 cells). In all cases the condition would be the same (find all cells with a value greater than than zero), but then replace with different values.

e.g. Cells with value >0 in range CX119:DQ138 replace with "NT", then cells with value of >0 in range DR119:EK138 replace with "NU"

I thought you could do it with find and replace by just selecting that range of cells but can't see how to set the conditional >0 bit.

View 9 Replies View Related

Return Maximum Value In Range Stated In Cells

Nov 18, 2007

What is wrong with this formula

=MAX(ADDRESS(VALUE(D2),1),1 & ":" & ADDRESS(VALUE(D1),1))

when D2 is 2, D1 is 6 and

=ADDRESS(VALUE(D2),1),1 returns $A$2

View 9 Replies View Related

Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

View 2 Replies View Related

Excel 2010 :: Color Fill A Range Of Cells If Specific Cells Not Blank

Feb 7, 2013

I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.

I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.

View 7 Replies View Related

Specific Cells Populate With Specific Numbers When A Value Within A Range Is Entered

Oct 9, 2009

Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.

View 14 Replies View Related

Count Number Of Blank Cells In Range Starting And Ending Will Cells That Match String Values

May 13, 2013

I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.

I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?

The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.

View 5 Replies View Related

Sum Range Of Cells Depending On Changeable Values In Other Cells

Aug 2, 2012

Assuming I have a data set like this :

column a column b
916 12,5
916 23,4
923 34,5
923 23,9
932 98,6
934 67,7

How can I get a sum of all cells in column b when values in column a are equal?

View 5 Replies View Related

Find Cells With Specific ColorIndex...

Feb 13, 2010

How can I change the code to search for cells with specific .Interior.ColorIndex

View 2 Replies View Related

Find And Replace Macro Based On Specific Cells

Sep 12, 2009

After much fretting over this (and many other Excel issues), I've decided to seek out some assistance. I've included an example with columns, because I'm not very fluent in Excel terminology.

View 5 Replies View Related

Looking Back Through Cells In A Column To Find Specific Text(s)

Feb 14, 2010

In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2. eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2. If the Destination is not LAX, then Column E equals Column D.

the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.

View 5 Replies View Related

Find Maximum. Some Values Are Represent By Text

Aug 8, 2007

I have a column of values the values can be "T", 0.00 ->any number to the nearest hundredth. I thought I had this problems solved a while back but after having time to do some quality control I found that anytime there is a value of "T" the macro does not work. I have had some great help with this in the past to get as far as I have but I still cannot seems to get it 100%. I have attached a sample file. The result that I would like to see is in the cell BH145 is "16,17,23" without the quotes of course.

When you open the worksheet you will see that the values of concern are precipitation values. These values are rounded to nearest hundredth. When there is a trace of precip however it is recorded as "T" therein lies the problem that I cannot seem to solve.

View 8 Replies View Related

Find Maximum Value From A Pre-filtered Range

Jan 19, 2007

I am looking for a way of finding the maximum value in column H for each row in a pre- filtered range using VBA.

For example, the result of of filtermacro1 may return several records derived from a database of several hundred records. In the example below, the name of the person is in column C, followed by their H column value.

Audrey Perkins, .5
Marc Bloomberg, 2.0
Matt Phillips, .5
Paul Pachson, 2.0

However, the H column value may not be the maximum value for the rows above, as there are other records for Audrey, Marc, Matt & Paul in the database.

If the names are matched to the max value in column H for each record it should read

Audrey Perkins, 1.0
Marc Bloomberg, 3.5
Matt Phillips, .5
Paul Pachson, 6.5

How would I query only the names in a pre-filtered range and obtain the highest value in column H for each ? These values then need to be copied to H7 in a sheet called Detention Register.

View 9 Replies View Related

Import From CSV And Add Values In Specific Cells?

May 8, 2014

i have a question regarding the import of csv-files. I've seen lots of working import-functions/macros but none of them. So here are my questions:

1) is it possible to import data from a csv-file into specific cells in my worksheet? Let's assume i have a csv-file with 10 INT-Values and they all should be placed in different rows/colums with no regular pattern.

2) If the answer of "1)" equals "yes": how is it possible?

(i thought about importing the csv in an so called "helper-worksheet" and use formula/references inside the table, but to be honest: That's not the solution i like to have)

View 5 Replies View Related

Coloring Specific Cells By Values

Jan 16, 2010

I'm new here and not really that familiar with excel functions and programming so please forgive me if this is not the right place for my thread

I am wondering how (if possible) I can make this little scenario work:

Lets say I have

cell (A1) with the value 2
cell (B1) with the value 3

Now somewhere else I wish to color some cell to create a 2 by 3 "block".

So coloring 2 cells horizontically (the value of (A1) and 3 cells Vertically (value of (B1)

View 12 Replies View Related

Find Maximum In Mutiple Columns & Delete All Values After

Dec 2, 2006

writing a macro to find the max value in each column and delete all the data points that come after it (or preferably: delete all the data points that come 2 rows down after the max, if possible). There will be many columns of data where the max will come at different positions in the column.

Instead of deleting post-max values, it would also be acceptable to just copy values from the beginning to the max to the same column in a new worksheet.

View 3 Replies View Related

Macro To Find Specific Text In Column And Copy Certain Cells In Same Row To Different Sheet

Dec 30, 2011

how to work macros or VBA

I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:

Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)

What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.

I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.

The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).

I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go

View 9 Replies View Related

Highlight Rows If Cells Contain Specific Values

Dec 24, 2008

I need to creating a macro to highlight entire rows if a cell in that row (C) contains specific values.

View 4 Replies View Related

Delete All Cells In Range NOT Containing Specific Value

Mar 1, 2014

Not sure if this is better suited for a macro or done via conditional formatting.

Trying to empty a standard template we use and of a certain range I need to empty all cells not containing N/A (typed not error).

View 2 Replies View Related

Adding Cells Over Specific Range?

Sep 19, 2013

I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...

A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)

And so on...

Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?

View 1 Replies View Related

Recalculate Cells In Specific Range

Feb 13, 2008

I have found the following piece of code which does almost what I want it to:

sub my_calc
range("A1:A25").calculate
end sub
However, the Range I want Excel to recalculate is, from the Active Cell, 20 Rows down and 81 Colums across.

How would this Range be defined in VBA, please?

View 9 Replies View Related

Find Specific Value In Array Which Appears Multiple Times And Average All Relative Cells

Jan 24, 2014

I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.

Example below: (Find value = 1 in A:A) ... 1 is found 4 times.

A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400

In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370

View 6 Replies View Related

Return Number Of Cells With The Most Consecutive Specific Values?

Aug 13, 2014

I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.

View 8 Replies View Related

Ignoring Specific Values While Selecting Multiple Cells

Dec 20, 2012

I have to correct a spreadsheet that takes a bunch of values and creates an average.

=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%

I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.

So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.

I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%

It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.

So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try

=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%

It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.

The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.

View 5 Replies View Related

VBA To Color Cells - Search Workbook For Specific Values?

Mar 22, 2013

I am trying to create a VBA to:

1. Search workbook for a specific values and then to color that cell with a corresponding color.

2. Search workbook for a specific values and then color other cells underneath (the next 3 merged rows after the cell containing the value) with a corresponding color.

View 3 Replies View Related

Finding Sum Of Cells That Have Specific Values In Adjacent Cell?

Apr 4, 2013

I have a list of data:

2
140

1
660

10
140

0.92
660

2
130

0.18
660

4
510

0.44
820

4
510

I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)

View 2 Replies View Related

Delete All Duplicates (all Cells) In Range Specific

Apr 20, 2013

I need a code to delete all duplicates (all cells) in the range A1: F100?

Exemple:
Before
31
81
43

[Code]....

View 5 Replies View Related

VBA Code - Delete Specific Range Of Cells?

Apr 15, 2014

I wanted to ask how to delete a specific range of data in a cell range using VBA

Here is a snipit of code I currently have:

Sname1 = Sheets("Numbers").Range("FX3").Value 'FX3 = cell F starting column letter

Range(Sname1 & Range("FR7").Value).Select 'FR7 = row number

If I added the following:

Sname2 = Sheets("Numbers").Range("FX4").Value 'FX4 = cell n ending column letter

Changed the code to the following: obviously this code is incorrect below:

Range(Sname1 & Range("FR7").Value) : Range(Sname2 & Range("FR7").Value).Select

View 3 Replies View Related







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