I have a spreadsheet which calculates times from previous columns, I need to include only one of 2 columns whichever is the greater and in the next column only add the difference of the greater column if it is greater then the sum of 2 other columns as shown in attachment
Time in seconds
A B C D E F G
17837423078B1+E1+F1+ THE GREATER OF C1 OR D1B1+E1+F1+ IF C1 OR D1 IS GREATER THAN E1+D1 ADD THE DIFFERENCE IGNORE IF NOT
17882423078B2+E2+F2+ THE GREATER OF C2 OR D2B2+E2+F2+ IF C2 OR D2 IS GREATER THAN E2+D2 ADD THE DIFFERENCE
I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).
Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.
Please see the attached example of my problem. I have TIME in column A in minutes and % in Column B. What I would like to do is search the % column against the value inputed into E2 (in the example 52%) and find the corresponding TIME value (Col A). However, where there is no exact match of the E2 value I would like to select the next greater value. For example as there is no 55% in my example the formula would move on to 57.27% and record 21 in D2.
I have spreadsheet with different 100s of columns of dates with 600 rows. The first row identifies which zone the data belongs to (North, South, East, West. NE, SW, SW1, etc...)
I want to write a formula to check how many dates in each column fall in 2015 or later years; This can be accomplished by writing a countifs formula.
Where it gets complicated is once i filter on the Zones;
I want the formula to give me the desired result - count of all CELLS where the year is 2015 or greater - WITH FILTERS ON.
I stumbled upon following sumproduct formula that gives count for visible cells, however when i apply the date criteria, i get incorrect result -
I'm looking for a formula that will add up the cells only when all three cells have a value greater than zero. In other words, if the last cell is zero I would like the value of the formula to =0 until data is entered
I'm trying to count all cells in a row after the last number that was greater than 0. See my example spreadsheet. I have put the answers that it should be in to show what I'm after.
I have made a spreadsheet that essentially works out a cost dependant on which department the work was completed in.
I have a seperate tab with the department names in column A and the charge rates in column B
On the second tab I have a spreadsheet that has columns A and B for hours used and material cost.
The third column is the dept name
The 4th column is the variable charge rate which changes dependant on which department is put in colum 3
The 5th Column is the total cost.
In the 5th column I have this formulae:
=VLOOKUP(F7,'Variable Costs'!A3:B16,2,FALSE)
This work fine.
However, the spreadsheet has about 200 entries per week and all the cells in column 4 and 5 are showing "#N/A" becuase there are no values for them to work with.
I don't like seeing this and normally will use the "IF" function to say that if column A and B are greater than 0 then do the sum, if not show "0"
This for some reason doesn't work with VLOOKUP. Not the way I am using it anyway
I want to divide the numbers from 2 cells, down a long column. Let's further say that in some instances there aren't any numbers in either of the cells. You get a divide by zero error.
So you have values in a1 and b1 through a10 and b10. You want to divide the value in the a column by the value in the b column and return the answer in the adjacent c column. You've copied the formula all the way down to c20.
All the cells from c11 to c20 will have divide by zero errors.
What is the syntax to check if both cells in columns a and b are greater than zero so that either an answer will be returned or just a blank cell will result, (when nothing is in one of the cells from a or b?
The following formula works for checking the status of cell A, how do I also check the value in cell B? =(IF(A5>0,A5/B5,""))
My goal is to look at a column of numbers and count the number of consecutive cells that are positive (or negative), while ignoring blank cells that may be in between them. Using the first formula on this thread. http://www.excelforum.com/excel-gene...-than-0-a.html
I was able to adapt it to my own data, but whenever I had blank cells in my data the results were not reliable. the formula i am using now is =IF(COUNT(1/((A1:A27<0)*(A2:A28<0))),MAX(FREQUENCY(IF((A1:A27<0)*(A2:A28<0),ROW(A2:A28)),IF((A1:A27<0)*(A2:A28>= 0),ROW(A1:A27))))+1,0). for a bunch of data in column A. With limited knowledge of excel I'm not exactly sure how this formula works, so I'm not sure how to tell it to ignore all blank cells. I attached an example that shows how it fails with the blank cells
I have 5 Sheets titles Quote 1, Quote 2, Quote 3, Quote 4, and Quote 5, and One Sheet called DataQuote. There s a qty column on all 5 Quotesheets at D7:D62. So that if i was to put a numerical value in that column From any of those sheets I need to Put on the DataQuote the Following:
Say i Have a quanity 5 on quote 3 on cell D9.
On the Data sheet I need to Show in A10 The Name of the Sheet, then stuff from Quote 3 A9:F9 in C10:H10.
alternatively if i went back and put something on Quote 1 Put those values in the above range moving down stuff from quote three, automatically updating itself.
I'm comparing budgeted dollars vs. actual dollars spent by month. So row 10 has 13 columns (column A is title and then B-M is Jan-Dec) with each cell containing a budgeted dollar amount. Row 26 is same setup but contains actual dollars spent for those months. I want to highlight any cells in row 26 that are greater than the same cell in row 10. So if H26 is $1,000 and H10 is $500, I want H26 to be highlighted in red. How can I do this without having to conditionally format each individual cell as I can't seem to make this work with a cell range in Conditional Formatting?
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount 5-10 items they receive 2% discount 11-20 items they receive 5% discount 21-50 items they receive 8% discount over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
I am working on a Golf League score sheet, and each week there may be a different amount of players that play. I have a weekly worksheet (attached) that contains the full list of golfers that had played that week and I only want to sort the columns for those golfers who have a score greater than 0 (this would be the ones who played that week). You can see that in week 1 I had more players than in week 2. So in my weekly worksheet I sort these sub grids by Score first. However I ONLY want to sort the rows that have a score greater than 0. Can this be done in VBA or am I stuck doing this manually every week?
I'm trying to List names for cells w/greater value in a table w/ 5 columns. name that has greatest value for each row should show in column B. See example booklet.
I have a list of numbers in two columns. All I want to do is that if the number in column B is larger than the number in column A I want it to be in red font. I know I need to use the Options>View - Zero Values.", "style="background: ...
I would like to have a complete column compared with its immediate next values and delete the values whose difference is more dhan 5.Im new to VB programming.
D
12.90 13.34 19.90 26.90 21.55
If dis is my set of data how should i use range values?
I have a row with values in successive cells. Some cells are blank. I want to conditionally format the cell text colour based on the value compared to the previous populated cell (i.e. compare A3 to A2. If A2 is blank, compare it to A1).
I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?
Formula to pull the lowest number that falls in Cells I2:L2 and the number must be 0 or Greater. Some of the numbers might be negatives and I am trying not to pull those numbers.
I have a sheet that I populate with data, and I would like to create a formula (or some code) that will look down column A, and if any of the cells contain a value >1 copy the cell and the adjacent cell (column B) and paste both values into a new sheet.
I'm trying to create a formula (in Sheet 1) that will return the most recent number of cells containing a value (i.e. not empty) in Sheet 2 Range AD2:A367 greater than zero.
I've tried using COUNTIF but that only adds the individual values, it doesn't 'start from zero again' when a zero value appears.
I am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)
The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.
