I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.
I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)
I'm looking to use MIN to look at a range of data in L:L based on matching certain criteria in A:A. In the attached spreadsheet in Totals!C1 I would like the MIN value from List!L:L for user "CHRISTOPHERJ" in List!A:A
View 3 Replies View RelatedI am trying, without success, to create a formula that will refer to a column of data and do the following in one step:
1. Find the minimum value in the range, and then
2. From that minimum value, count back “up” the column the number of occurrences, including the minimum value, until a zero is reached. The zero should not be counted.
For example, if the values in A1:A6 are 1, 0, 2, -1, -2, 1, the minimum value is -2 and the count would return 3. (i.e. 2, -1 and -2)
What is the syntax for the formula for the minimum number, greater than zero, in a series of numbers.
I have a row of numbers, including some zeros, but I want the minimum number excluding the zeros.
I have 3 columns L, N and P
I would like to return the MIN value(column R) between the columns
except where the MIN = zero then just leave the result as blank.
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies View RelatedI want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
I need to determine if any of the cells selected by the user have an indent level equal to 1 and if so then have them confirm the action. the below works well if the user only selects one cell but if the select 2 or more and any of the selected cells has an indent level greater than 1 it doesn't show the confirmation.
View 3 Replies View RelatedI have the following formula which works perfectly unless one of the cells has a value of zero;
=MIN(SUM(AD13),MIN(SUM(AK13)))
When AD13 = £60.00 and AK13 = £94.00 (or vice versa) the formula returns £60.00, which is correct. BUT when AK13 = £0.00 obviously the formula returns £0.00, but I would like the formula to still return £60.00.
How this can be done?
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.
Here is the ....
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)
If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.
This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies View RelatedWorking in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
View 2 Replies View RelatedIs there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies View RelatedWhat I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
I am making the monthly schedule for my colegues. I wonder if there is a conditional formula that can highlight a row with 10+ cells if in one of them I type SUNDAY or something.
I am attaching an example of the schedule. I thing you will get my point.
I want to add some colour conditional formatting to the cells in column C, dependent on their variance to the figure in column B. I've got the basic gist of applying the rule to the one single cell, but I'm not sure how to apply it to all the cells in the column. I have tried "dragging" it down, but it then just gives me the variance to the original row.
View 2 Replies View RelatedLock cells based on conditions? For example, in my spreadsheet I need my cell J42 to stay locked at all times unless my cell C12 reads "Klongtoey".
View 10 Replies View RelatedI am trying to create a form that has multiple drop downs, which are dependent on answers from other drop-downs. I'm trying to make it super user friendly and have the cells lock, so that the user can tab through only the items that they need.
I have it almost down, but I'm not sure how to do it for multiple cells. The closest I come is one big long if,else statement which runs through the whole thing on every cell change.
I have an example of the spreadsheet attached.
If F1 says "Other", I want H1 to be unlocked
If F2 says "Y", I want F3 and F4 to be unlocked
IF F4 says "Other", I want H4 to be unlocked
i want to know is there any way to set up conditional format to the cells used in a formula which is in another cell.
View 8 Replies View RelatedI have had a look around and found some answers to this question but not quite as complex and I don't know enough to adapt them correctly.
Basically AX35:AX239 contain a formula which returns "TRUE" or "FALSE" dependant on certain values in the row, what I need is for the corresponding H, I & J cells to be unlocked on each row if the outcome is "TRUE" and no action if "FALSE".
what would I do if I want cell B:1 to be formatted a certain way if the value in A:1 is a certain value?
View 9 Replies View Relatedi want to hide columns based on values of particular cell in that column.
View 9 Replies View RelatedI have to calculate the difference of two cells in an excel sheet if the first cell value is greater than the second one. If the first cell value is smaller than the second one then the difference should be shown as zero.
View 9 Replies View RelatedI have a condition whereby if cell C7 has a value entered, then cells D7 & E7 cannot have values entered. Like wise, if cells D7 & E7 have values, them cell C7 cannot. Cell F7 would be the product of the calculations using the data either in only C7 or only in D7 & E7. Please see the sample attached worksheet with my problem elaborated.
View 9 Replies View Relatedi'm trying to reference a cell value in the column to the left of a "min" value:
-A-------- B
1200------20
2400------15
2300------18
900-------22
i'm using the "min" function to select min value from column B (15) but only as a means to use the A column value (2400) in another formulahelpful
I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?
View 1 Replies View Related