If Statement (equal To And Greater Than)
If A7 is equal to and greater than 95% than B7 is equal to "A". If A7 is equal to 91% but less than 94.99% than B7 is equal to "B"...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
The Smallest Value That Is Greater Than Or Equal To
I am looking for a function like MATCH if the match type were set to 1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.
View Replies!
View Related
Greater Than Equal To With Autofilter
I am creating my first Userform and having some problems. I take the data supplied by the userform and try to match it as closely as possible to a row of information. Currently I am using four cells to autofilter my spreadsheet data. Two of the cells I am looking for a exact match. The other two cells I am looking for the number that has been input or anything greater than it. Here is the code I have come up with...
View Replies!
View Related
If Greater Than Or Small Than, Or Equal To
I have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.
View Replies!
View Related
Less Than, Greater Than, Equal To Conditional Formatting
i'm trying to create a markscheme progress sheet. You take a childs actual age in months and years (ie 9.8 for 9years and 8months). They then, in theory, should be at that age level for reading and spelling. I have the age in B4, the reading in C4 and the spelling in D4. If i use CF to say i want C4 to be red if it's less than B4, Amber if it's the same and green if it's over, as soon as i okay it, C4 goes amber, because it's equal to B4, which is blank. I can get the colours to work, i just can't figure out what to put to turn the amber off when cell B4 is empty. I've tried as many combinations as my little amount of excel knowledge can cope with.
View Replies!
View Related
VLookup :: Equal To Or Greater Than The Minimum Amount?
I am revising a spreadsheet to automate some cells and size cable for my job. I am currently using the VLOOKUP command to search a table for the proper ampacity and return a cable size based on the minimum circuit amps. The problem however is the VLOOKUP command searches for a number equal to or less than my circuit amps and returns this cable size. I need it to return a size equal to or greater than the minimum circuit amps. How do I get it to lookup something equal to or greater than the minimum amount?
View Replies!
View Related
Delete Rows Column F If Greater Than Or Equal To Zero.
Starting in cell F3, if Column F is greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Then go to Column F in that same row that had contents in Column A, if the contents in that row of Column F are greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Persist with this pattern until every row in Column F has been checked.
View Replies!
View Related
Counting Number Of Dates Equal To Or Greater Than A Said Date
In my Excel spreadsheet I enter todays date in a single cell (A2), then I list various dates that jobs come into shop in other cells (A8:A108). I have cells ( F8:F108) where I have been manually entering an asterik (*) for those jobs equal to or greater than five days old in cell (A2). Is there a formula that can do the math for me? I've tried Excel help but to no avail.
View Replies!
View Related
IF Statement: Find The Greater Than Value
I have a value in E12, and i need a formula that looks at the value and if it is equal to or greater than 5, then the output should be E12 x 500 +1000, but if the value in E12 is greater than 5, then the output needs to add the original 5 x $500 and now include all greater than 5 to be x by $250 + 1000. I got this far, but if the value is greater than 5, i don't get the original 5 * 500 that i also need. =IF(E12<=5,(1000+500*E12),IF(E12>5,(1000+250*E12)))
View Replies!
View Related
IF Statement Within A COUNTIF Statement: Cell In Sheet "Summary" Count The Number Of Cells In Column DX Of Sheet "Analyses" That Are Greater Than 0
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary." (In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 164 correspond to Subject 1, columns 65128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly: =COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0") (Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View Replies!
View Related
Values Which Are Equal Not Treated Equal
I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.
View Replies!
View Related
Record Greater Than 50 In A Cell So It Reads As Greater Than 50
I have to make a table that shows that a if someone purchases less than 5 items they receive no discount 510 items they receive 2% discount 1120 items they receive 5% discount 2150 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.
View Replies!
View Related
Utilize A Select Case Statement In Target Intersect Statement
I am currently using an Intersect statement in a worksheet module to perform two things: 1. Insert a time stamp into row 2 when row 1 has a price inserted 2.To clear that time stamp if the price is deleted at some later date. My problem is with the time stamp value being deleted by the user. If I try to clear the price (now that the time cell =empty) I get a Runtime error 91  Object Variable or With block variable not set. I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View Replies!
View Related
Switch Statement Is Too Long For VBA. To Make The Switch Statement Work Over Two Lines
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears. "Invalid procedure call or argument" I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View Replies!
View Related
Equal To Or Less Than
I have a quite complex list of variables to put into a formula to come up with the correct number and I need some help to figure this one out. I don't know if it would be easier to set up a chart and then index the chart to do a lookup or how I need to go about doing this to achieve the most efficent solution. I need the following to go into one formula: 1. =< 24 needs to show the number 24 2. =< 30 but > 24 needs to show 30 3. =< 32 but > 30 needs to show 32 4. =< 36 but > 32 needs to show 36 5. =< 40 but > 36 needs to show 40 6. =< 48 but > 40 needs to show 48 7. =< 50 but > 48 needs to show 50 8. =< 60 but > 50 needs to show 60 9. => 60 needs to show 60 I am trying to set up a spread sheet to figure square footage of plastic laminate countertops, but the plastic laminate only comes in several stock sizes : 3x8, 4x8, 5x8 3x10, 4x10, 5x10 3x12, 4x12, 5x12 So if I have a countertop that is 25 sq ft., I cant use a 3x8 (24 sq. ft), so I need to go up to the next size which would be a 4 x 8. I might need to use a ROUNDUP function with this formula too? I already have cells set up to figure the depth, backsplash height, and front edge height and then give me the squage footage of that.
View Replies!
View Related
Not Equal To Operator
revised this code to reflect the "not equal to". I want the user to only enter "In" or "Out." Therefore, If cells b19:B26 does not have either word, then the message box will prompt the user to enter the correct word again. I think my code needs to be revised into some kind of loop.
View Replies!
View Related
Sum IF (2 Equal Values)
I've created the following function that chooses the maximum value from a set of cells then inserts theappropriate row number (within a table) into a new cell. =IF(J27=0,"?",IF(J27=J19,1,IF(J27=J20,2,IF(J27=J21,3,IF(J27=J22,4,IF(J27=J23,5,IF(J27=J24,6,IF(J27=J 25,7,IF(J27=J26,8))))))))) It's working fine until I have 2 cells with the highest value. The above statement is entering the first cell that meets the criteria in the new cell but ignores the fact there maybe 2 (or more) of the same value. How can I get both (or all) to be entered in the same cell? Is there a better way, maybe highlighting all the rows in the table that equal the max figure?
View Replies!
View Related
Several Criterias Equal Different #
I am having trouble creating a formula that will do what I need it to do. In C4 the I already have a formula that states if B4 is greater than 94.99% then it will equal 5. What I need it to do is if it is equal to or greater than 95% then =5. Also if it is equal to or less than 94.99%  92% =3 and if less than 91.9% =1. Here is the worksheet I am using....
View Replies!
View Related
If Equal To Or Less Than Formula
I am trying to set a scorecard up where it will give points depending on the % actual. For example: if less than 60%, then 0, if equal to or greater than 60% but less than 75%, then 1 if equal to greater than 75% but less than 80%, then 2 and so on. I have been playing around with the example below, but can't seem to get it working. [php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php] Maybe it 's because I pickled my brains last night.
View Replies!
View Related
IF Statement, MAX Statement, And Return A Value
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C. i.e. Column A  Column B  Column C AB  0:02:22  04/14/2007 CW  0:03:13  04/16/2007 CW  0:01:42  04/13/2007 Thus, the value that should be returned is "04/16/2007".
View Replies!
View Related
Ifstatement Inside An Ifstatement
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2A1)*24 But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2A1)*24) Now, if I want to change "(A2A1)*24" to another ifstatement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first ifstatement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
View Replies!
View Related
If Statement Contained Into A Sum Statement
I have a problem with a formula inserted with control+shift+enter The formula is MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999)) In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values. Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G. The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D. I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
View Replies!
View Related
Equal Value & Formatting Between 2 Cells
Is there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values? As in this for example: Pieces of Paper (Blue) I would like to keep the formatting for (Blue) , color and bold, but I have not figured out how to do this.
View Replies!
View Related
Why Aren't These 2 Cells Equal
I have a date on one sheet, and what I thought was the same exact date on another sheet. I want to vlookup off the date, however the values aren't the same. i.e. when I check to see if they = I get a false. They're both formated the same, custom date m/d/yyyy h:mm, and when I ran the check to see if one was text (t=(A1)) I get confirmation that they are both nontext!
View Replies!
View Related
Place The Name Before Equal Sign
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort? Private Sub ConditionalRowDelete() Dim NumRows, iLine As Integer ActiveSheet.UsedRange.Select NumRows = ActiveSheet.UsedRange.Rows.Count Dim CircuitType As Range Set CircuitType = Range("C" & iLine).Value For iLine = NumRows To 2 Step 1 If Range("A" & iLine).Value > "6999" Then Rows(iLine).EntireRow.Delete End If Next iLine For iLine = NumRows To 2 Step 1 If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then Rows(iLine).EntireRow.Delete End If Next iLine End Sub
View Replies!
View Related
Strange Characters Equal EOF
I made a userform that spits out 'logs' in a more readable easy to understand. It basically shoves everything into a nicely organized list box. I have a load button that if pressed accepts .csv's It works perferctly for practically every type of .csv that contains these logs however 1/100 times the logs contain korean or chinese characters which cause the program to crash. I gone through some debugging and I found out that the characters that cause it to crash happen to equal the EOF character. So not only does it spit an error on that line, it stops loading anything after that point. Here is my CheckFileSize function that runs first to tell me how long I should make my list. Private Function CheckFileSize(ByVal TempFileName) As Double Dim TempData As String Dim SizeCount As Double how to do this besides loading it into a worksheet first?
View Replies!
View Related
To Have Cell Equal To Autofilter
I have 2 worksheets, and I was wondering if I can have a cell from Sheet2 equal to the subtotal of the Autofilter criteria of a column in Sheet1. I do have something working with macros but it takes a long time. This would make things a lot easier.
View Replies!
View Related
IF Formula Based Around A Value It Does Not Equal
Is it possible to do an IF formula based around a value it does not equal? eg I have one column, that is perforning a SUMIF providing that a certain cell is equal to a particular constant. =IF([cell]=[value],SUMIF([range],[criteria],[sum_range])) In the next column I want to do it the other way round so that: =IF([cell] DOESNOT=[value],SUMIF([range],[criteria],[sum_range])) Is it just a simple symbol??
View Replies!
View Related
Average If Cells Are Equal
Columns L, M and N are always fully populated. Columns D, E and F may have some missing values, along with H, I and J. Lets just concentrate on D. I need to create a formula where I get the anser in C1 This is an average of L only where D has a value.
View Replies!
View Related
Formula Equaling Zero Is Not Equal To 0
I have a simple formula which subtracts one cell from another. I then have an if and statement which checks if that cell is equal to zero. If so, continue processing. The problem is that the formula always reads the cell as not equal to zero although the result of the formula contained in the cell does result in zero.
View Replies!
View Related
Select Cells Equal To Zero
How can I select all rows where certain cells are equal to zero? (i.e. in Column A 1 Bob 2 Joe 3 Smith 4 0 5 0 6 0 I want to select rows 4, 5 & 6. I've used Go To>Special:Errors to write a macro to hide all rows with errors, but I also want to hide all rows that have zero values
View Replies!
View Related
Hide Row If Three Cells In That Row Each Equal Zero
I am trying to hide rows if cetain cells in that row equal zero using a button on the page. Each cell has lookup formulas that will return a value. If coulmn B,F & I have a value of zero I want to hide the row. As of right now I am using the following code but I keep getting the following error message runtime error '13': type mismatch
View Replies!
View Related
If A Certain Cell Does Not Equal Zero Then Run Macro
if a certain cell does not equal zero i want a macro to run. But i'm having trouble getting the logic right. here's what i have so far Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("u82").Select If Active.Selection 0 Then Call unhide_addABBR ElseIf Active.Select = 0 Then Call hide_addABBR end sub
View Replies!
View Related
Set Array Equal To Part Of Another
Is there a way to set an array equal to part of another 2D array. If say I have an array with 5 columns. Is there a way to break that up in to two arrays one with 3 and one with 2. Even getting it to 5 1D arrays would work. I bring it in as 1 array but I need the information in different places. This is actually only the output. So if there is a way to send only part of the array out that would work to. I know you set the range smaller than array with range=array, but that still doesn't get me where I need to be. This is obviously without simply going through a loop. I could set it to 5 singles with a single nested loop I know. I was looking for a nonloop way.
View Replies!
View Related
Set All Values In Array Equal Same Value
how to set all values in an array to be the same value. Currently I am using code in a For/Next loop like the following to do this: Sub testing() Dim ArrayToAllHaveSameValues(1 To 30) As String, i As Integer For i = 1 To 30 ArrayToAllHaveSameValues(i) = "Value" Next i End Sub
View Replies!
View Related
