I want to be able to count how many instances Primary Price is >= Price 2. And, secondarily how many times Price 1 was, at a minimum, >= to Price 2 and then also >= Price Price 3.
Ultimately I want to compare Primary to 100 or so "Prices" over a years time
I figure it would be a sumproduct function, but can't get it dialed in
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.
I have a column of different "finish" times (hh:mm:ss). I would like to have the adjacent column look at all of the "finish times" and post their ranking from 1st place to last.
View 9 Replies View RelatedI have problem to calculate the time which is more than 23:00:00hrs in a file. I need to compare the benchmark time and the difference of time(start time and end time) and show pass/fail depending on benchmark set time. For few of the times which is more than 23:00:00 hrs the format is taking different(example: for 49:15:48, it is showing 02/01/1900 01:15:48 in formaula bar) for which i am not able to use formula and know pass/fail.
View 4 Replies View Relatedhow to roundup and total time using formula? specifically with the format mm:ss:0? i have 3 columns with these formats and would like to add them up but can't seem to get the right formula.
View 4 Replies View RelatedI have made a userform where I calculate how long time an operation takes. If the time fe.g. is 25 hours and 24 minutes then I get the result 1:24. I have attached my userform as it looks now. If you write 540 in the bar and 550 in volume and press "Beregn" then "Norm tid + 10%" will write 1:24 and not 25:24. Is it possible to have the Userform to write 25:24 or 1day and 1 hour and 24minutes?
View 2 Replies View RelatedI 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 would like to change the name of all cells withen xrange with a text value greater than nothink.
View 4 Replies View Relatedis possible to have a cell's text color change if the value of the cell is greater than a certain amount. For example, if the cell is $200 or more the text would change to red.
View 3 Replies View RelatedIf I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use. I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.
View 4 Replies View RelatedI would like to create a cell that will tell me if a dimension has failed.
I have a tolerance that the value must be between and if it is not then i would like a cell that will input an X
I’m trying to write a macro to look down two different columns in my work sheet and if the is a value >0 then copy a set range of cells to another sheet,
I need to search column (k) and (x) range (“K2 : K147”) and (“X2 : X147”) in (sheet1) for values >0 if there is, then copy from (“f : m”) if it is found in the (k) column, or copy from (“s : z”) if it is found in the (X) column, and paste value only to the next empty cell in sheet2 . Sheet2 is empty so (A) on down is fine for paste range. There is a spin button in the copy range column (L) and I don’t want that picked up in the copy. Manually I (paste text) only but I think (values only) will do the same thing.
[Code] .........
I have one sheet where I am entering the necessary parts of the particular order. Once I have it completed I need to run a macro to copy only the elements that I have marked.
From rows 3-17, if cells in E3-E15 are greater then 0 then I need the rows
from Column "C" to Column "G" and Column "I" copied to another sheet. With rows 20 - 97 if cell in column "E" is > 0 then copy the relevant row from column "B" to column "E". I am attaching the file.
I have three spreadsheets.
I need to have an automatic macro where if the Cell Value in SPREADSHEET 2 for example Cell J5 is greater than 0 (Zero is the default value in the cell).
Then the macro will open up a msgbox that says "You Are Not Eligible" in SPREADSHEET 1.
I would like the macro to run on its own if the cell value is greater than 1
i have data comming into the excel sheet using sql,
and j1005 cell calculates the entire data displays 1 if condition of j1005 is met else display 0 . the data refreshes on its own and j1005 keeps check every time and display the value 1 or 0
I have a football score system and wish to compare the values of various cells - 2 if possible, but more if not possible.
Essentially I have a list of predictions for the score of a game (e.g. 2-1, 1-0, 0-0, 2-2 etc) in a spreadsheet, and I have a cell where I enter the correct scoreline (e.g. 2-1). The way the points system works is that if the prediction is correct (i.e. 2-1) then that player gets 3 points. If the result/outcome is correct but the scoreline is incorrect (i.e. 1-0) then the player gets 1 point. If they result/outcome and scoreline are incorrect, then that player gets 0 points.
Ideally I would link to compare 2 cells - predicted score and actual score, where the format of the scores is entered as 2-1.
However, I feel this may be easier having to columns for the score - Column A = 2 Column B = 1. In this way the scores only need to be compared cell by cell, however they would need to be compared by value for the correct outcomes.
I'm populating a userform combobox this way :
For i = 1 To npoints
Me.Combobox1.AddItem i
Next i
Meanwhile I'm populating a cell with a value from a listbox:
Worksheet1.Cells(rowindex, 6).Value = Me.Listbox.List(listindex - 1, i)
I don't understand how to make this return TRUE:
Worksheet1.Cells(rowindex, 6).Value = Me.Combobox1.Value
I tried playing around with format( , ) but without success.
I have a quick question: I want to compare two cells' formats with as short programming as possible, so the program runs faster.
I have the current code to compare the formats and give the variable "Result" the value "FormatChange" if the formats are different.
To make a long story short, I'm trying to compare formulas. I was able to copy the formulas as values.
There are hundreds of lines, each one with the formulas pasted as values, like '=SUM(G132:I132) / G30. What I want right now is to compare the text.
So, if for example:
the line for both parts of the pasted sum formula like '=SUM(G132:I132) / G30, I want a number 1 to be written in the adjacent cell (if '=SUM(G132:I132) / G30 is in A1, 1 should be written in B1).
However, if its is written '=SUM(G132:I142) / G30, than nothing should be written in B1.
Moreove, the line could have from 1 to characters. So it could be '=SUM(G1:I1) / G30 or '=SUM(G13200:I13200) / G30.
is the same, like of A1 is '=SUM(G1322), I want a number 1 to be written in B1
I am currently using excel 13' and am having trouble inserting rows once the value in column b changes. My goal with this procedure is to insert two rows after each change in value in Column b (i.e. b2, b3, and b4, all equal 123 however b5 equals 124) Below is the code.
Dim cell As Range
Set i = Range(Range("A1:I1"), Range("A1:I1").End(xlDown))
Set bsort = Range(Range("B1"), Range("B1").End(xlDown))
Set ISort = Range(Range("I1"), Range("I1").End(xlDown))
[Code]....
'Need to Identify which cells are not equal to the one above in column B (bsort = range)
For Each cell In bsort
If cell.Value cell.Value - 1 Then
ActiveWorkbook.ActiveSheet.EntireRow.Insert
ActiveWorkbook.ActiveSheet.EntireRow.Insert
End If
Next cell
End Sub
I have a spreadsheet that records "Audit Date" in column C & "Audit Due Date" in column D. "Audit Due Date" is always 2 years from "Audit Date". I think I learnt the correct forumla to use =EDATE(C2,24). What I would like Excel to do is to highlight "Audit Due Date":
Red: If the audit was completed 1.5 years+ Amber: If the audit was completed between 1 & 1.5 years Green: If the audit was completed less than 1 year The colour key will show me which audits need chasing.
how to include a condition in the condition? What I mean is in this set of numbers:
1000(A) 1000(B) 1000(C) 1200(D) 500(E) 1000(F)
I want excel to point out (with a 1, True, x, whatever) if (B) is greater than (A) by 10% or more and if (C) is greater than (B) and so on. I know I cant use =if(C>B,1,0)...Im just unsure how to include the % in the function
I have a protected worksheet with most cells locked and some that are unlocked. I also know the password to unlock the sheet. VBA code to monitor a cell(B29 in my case) and if it has a value of 6.00 or more than it will unlock cell B34?
View 7 Replies View RelatedIn my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I have a lot of cells containing numbers. Some numbers are small, some are big.
I want to divide all numbers which are greater than 300 by 1000.
IF cell A2 > 300 THEN divide by 1000.
How to do that? Is it possible to convert value in the same cell like using conditional formatting principle?
I want to count the times that a value in one column has a value of one greater than the value of the corresponding cell in another column.
(For example, A1=1 and B1=2, A2=1 and B2=2, etc.)
I've tried =COUNTIFS(A1:A180, (B1:B180)+1) and =COUNTIFS(A1:A180, "="&(B1:B180)+1) with no luck.
(It gives me a value of zero, but I can tell just by looking at the numbers that there should be at least a few times when this event occurs.)
I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.
Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.
I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.
I'm creating a POS system for my wife's business. The workbook has three sheets.
On the first sheet are all the articles that her shop sells.
On A1 to D1 is the following text:
Quantity Discription Price Totalprice
B1 to D1 is already filled in. When a customer buys something, a employee only has to fill in A1, how many of what item the customer buys.
So it is possible that only 5 items have a number filled in, in A1.
Now, i have a vba, which has to search all the items which have filled in quantity and then copy row A to D to a sheet called Receipt. (Later I want to print this receipt)
Now this vba work when all the filled in cells are above each other. When there is a gap between the cells, it only copies the first row.
Here is the VBA:
Sub test()
Dim r As Range, c As Range
With Worksheets("Kassa")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In r
If WorksheetFunction.IsNumber(c) Then
Range(.Cells(c.Row, "A"), .Cells(c.Row, "D")).Copy
[Code] ........
When I change the second A1 in the fourth line to A200. I works, but is extremely slow. It's almost like if Excel is responding anymore. Also, when no quantities are filled in, it's also slow.
Basically, I have data that have the titles:
Year Max allowed A B C D E F
1999 55 45 23 60 53 53 57
2000 59 45 23 60 53 53 57
2001 51 45 23 60 53 53 57
And data for several years. Each year has a particular maximum allowed exceptions per process (A, B, C etc)
I basically would like =countif(columns that contain A through F, for values greater than Max allowed for that specific yeat)
Is there a way that I can get the countif to recognize the value under max allowed as the "tripping" value for the row A through F?
So I would get a value of 2 for row 1999, 1 for 2000 and 4 for 2001
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?
View 3 Replies View Related