Formula Greater Than/Less Than
I need a formula, If the number is greater than 1 and less then 30, it returns Yes, if not, then no.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Minimum Formula Greater Than To Zero
I have a range of weekly sub totals that get entered each week for the year. o8:bn8 As each week are entered I am trying to find the lowest week's production (using the =min formula) that is above 0 (weeks not yet entered appear as 0) the problem is that it keeps defaulting to the next column once a number greater than o is entered.
View Replies!
View Related
Greater Than Less Than Formula For Time
I have a time value h:mm in one cell and I have a range of three cells that I want to display values depending if the time is: under 12 hours over 12 hours and less than 24 hours over 24 hours and less than 36 hours over 36 hours I have been using the following formulas where A1 holds the time value: =IF(A1<12,"true",0) =IF(24>A1>12,"true",0) =IF(24>A1<36,"true",0) =IF(A1>36,"true",0) None of which seem to work
View Replies!
View Related
Create Formula To Add 0.2 To Cell Value If Its Greater Then 0
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.
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 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.
View Replies!
View Related
Formula That Allows Me To Type In Their Hour Work If It's Greater Than 40 Hours
I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent. We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2) My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours? Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00 If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2
View Replies!
View Related
'greater Than >' Formula & Result
Column B is basically 550 points + column C = Base Roll. We need to introduce a cap so no more than 1000 points can be displayed/earned. So column G adds all points & column F caps. So far it's all ok. Now when i add a value to column E (points spent) those points are deducted from column G instead of the capped 1000. Entering a value of 500 displays 800 in column F instead of 500.
View Replies!
View Related
Greater Than / Less Than
I'm struggling to complete this formula. No matter what the entry in M30 is (which is a concatenated formula from another sheet), I only get the highest response, which is 18. I'm assigning a risk score based on a dollar amount. The formula is: ....
View Replies!
View Related
IF, THEN, Greater Than, Less Than
Say I need to figure out bonuses based on income. If the income is less than 100,000 then the bonus is 5%. If the income is between 100,000 and 249,000 the bonus is $5000 + 6% the amount above 100,000. If the income is between 250,000 and 499,999 the bonus is $14000 + 7% the amount above 250,000. If the income is over 499,999 the bonus is $31,500 + 8% the amount above 500,000 What is the formula I need to enter to make this work.
View Replies!
View Related
If Value Greater Than
I have a Macro of 55 Columns and 2000 Rows I need to change the value in several Cells of the row in which the value of AZ is 200.01 or more. I need the following if ANY Cell in AZ2:AZ2000 is equal or greater than 200.01 then the following Cell in that Row will equal the following: AG = 20 AW = 11 AX = " " (BLANK) BC = N I have attached an example of the spread sheet with Macro embeded and how it should look after the above is run. I do not know if or how to I need to tag the macro within the attachement.
View Replies!
View Related
Greater Than Zero
Need to sum that won't work. i'm using: A B C 00:00 07:00 =if(A1>0,B1,"N/A") 07:00 04:00 =if(A2>0,B2,"N/A") 00:00 07:50 =if(A3>0,B3,"N/A") 00:00 06:50 =if(A4>0,B4,"N/A") This doesnt seem to work though using time formats. Column C just brings through Column B no matter what is in Column A. I've attatched an example below.
View Replies!
View Related
SUMIF: Value Is Greater Than Zero
I have this formula to tell me how many times in a given column the value is greater than zero: =COUNTIF('Data Entry'!AY3:AY117, ">0") But I also need to know how many times the value in AY3:AY117 is greater than zero PLUS how many times the value in AZ3:AZ117 is also greater than zero I've scoured this wonderful website and have got as far as: =SUM(IF('Data Entry'!AY3:AY117,"> 0"+SUM(IF(AZ3:AZ117,"> 0")))) but I'm clearly still way off.
View Replies!
View Related
Add Value If Greater Than
I am trying to create a formula that will automatically calculate greater than or less than and then add x or y depending. I am shipping some items and we can fit 100 or less in a small box that weighs 3oz and 100+ in a bigger box that weighs 6 oz. I want the formula to look at the quantity and determine if the quantity is 100 or less it should add 3 and if greater than 100 then add six. I tried tons of google searches but can't seem to figure it out. This is the first forum I could find that I figured might be able to help. I have been doing this all by hand in multiple columns...
View Replies!
View Related
Count If Greater Than But Less Than
I want to do is count in a column numbers greater than 13 but less than 20. I am also trying to write another formula that counts numbers equal to or higher than 1 but less than 12. In other words, I do not want this count to include any cells that contain 0.
View Replies!
View Related
Date Less Than Greater Than
I have a list of dates B2:B248. All the cells have been formatted to date type dd/mm/yyyy. I'm just trying to count the number that are earlier than a certain date. I thought a COUNTIF function would work! I've tried DATE, DATEVALUE etc. I can work with the dates ie B2 (which would have 23/08/2007)-1 comes out as 22/08/2007. However when I try if(B2<B3,"lower","higher") it gives me a false reading - even when B2 is earlier than B3. How can I check if my list of dates is earlier than a specified date?
View Replies!
View Related
Minimum Greater Than Zero
I have a table populated with equations. I need to write a function to find the smallest value in that table. However, I want to ignore the zero values. From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.
View Replies!
View Related
Multiple Greater Than Less Than
I want to calculate a mark-up on one cell. If it is under a set amount then I want the mark-up to change. If it is then over a set amount but below another, then I want the mark up to change again, etc. I have got this far (eg. below) but the calculation does not work properly when the value in "A3" is over "1". =IF(A3<=0.49,A3/0.2,IF(A3>=0.5,A3/0.25,IF(A3>=1,A3/0.35,IF(A3>=2,A3/0.5,0))))
View Replies!
View Related
Greater Than Or Less Than (Plus Or Minus)
Here is my formula. =If(Sheet1!J5=$J$4,0,(Sheet1!$J$4-Sheet1!J5)*Sheet1!$J$1) It is perfect, except it doesn't have one final step. What I need it to do is be able to do that ONLY if it is greater than or less than by a specified amount. So there needs to be a modification of ... Sheet!J5<> (but by 100 or any other number that I set) $J$4,...
View Replies!
View Related
IF To Calculate If One Date Is Greater Than Another
I am having great difficulties getting the following formula to calulate wether the date in cell f2 is greather than of less than the given date. =IF(F2<="18.11.06", "No Cover", "Under Warranty") f2 = 14.09.02 This does produce the wanted result of "No cover" however if the date is 14.09.07 and therefore greater the 18.11.06 i will not get the expected result of "Under Warranty"
View Replies!
View Related
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
Select Case - Greater Than / Less Than
DayCompare = Day(Date) Select Case DayCompare Case Is < 7 And Sheets("Roster").Range("D8") = "N" MsgBox "Hello." Case Is > 15 And Sheets("Roster").Range("D8") = "N" MsgBox "Goodbye." Case Else End Select I thought this would work OK, however even though today is the 10th, and therefore ignored by this statement, it is picked up in the >=15 statement
View Replies!
View Related
Greater Than 7 Nested If Formulas
I undrstand that you can only ave a certain amount of nested IF statements within one formular, is there a way round this? What i'm looking to do is this. VLOOKUP(VLOOKUP(location,IF(postcode="BA",LocationBA,IF(postcode=" BS",LocationBS,)),2,FALSE) I have 13 options that can be chosen, but only have room for 6 nests of which BA and BS are currently above. what would be the best way to have all 13 within the lookup table array?
View Replies!
View Related
Textbox Input Greater Than 9
I have a userform with a textbox1 on inputting the numbers 1-9 the code fires to open that qty. of worksheets it works fine. But, why if the user wanted to input 10 it reads the 1st number 1 and only opens 1 sheet. I need it to open another userform if 10 or greater is input. Option Explicit Private Sub Userform_Initialize() 'CALCULATES # OF SHEETS ThisWorkbook.Unprotect ActiveSheet.Unprotect
View Replies!
View Related
If Greater Number Msgbox
I like to do is if cell H3 is Greater than cell A35 to promt the message box below. But if less than do nothing. And if possible if less than to look at sheet 2 cell A35 and do the same. If WorksheetFunction.Sum(Range("H3") >= ("A35")) Then Exit Sub MsgBox "QTY. MUST BE GREATER THAN END FEQUENCY", vbOKOnly
View Replies!
View Related
Extract Data If Value Is Greater Than Zero
I built an estimating spreadsheet for the electrical construction industry and am trying put together a "Materials List" on another worksheet. I want the materials list to display materials which have a value greater than zero. Example, the 1st worksheet is my estimating worksheet which contains a list of 30 materials. The 2nd worksheet is a "Bill of Materials" that I would like to display in a proposal format to the customer. I only want to show them materials that have a quanitity of more than zero from the estimating worksheet.
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
Message Variance Greater Than 5
I have a workfile with several worksheets. I have the word "variance" appearing in some of the rows in column B and a value in column C which is in the same row as the word "variance" for eg row B19 Variance C19 10 I need VBA code that where the value which is in line with the text "Variance" is greater than 5, the name of the worksheet is listed in the worksheet named "Variances" See Example below ******** ******************** ************************************************************************>Microsoft Excel - Statistical Data.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC13C14C16C18= ABCD131110XFORD*UNITS*IN*STOCK86*141111XMazda*N/V*UNITS*IN*STOCK17*15****16Total*103*17****18*Variance0*East* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Copy If Cell Greater Then Zero
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.
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
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
Trying To Average The Last 10 Values Greater Than 0
Members in our club enter competitions each month and receive scores. I want to be able to average the top five scores from the last ten competitions each member entered, keeping in mind that not all members enter all competitions and will score 0 in this situation. Its easy if members enter every competition, but I want a formula that will work also for when members may only enter 10 of the last say 15 competitions and I still need to find the average of the top 5 scores. Member Name is in A1, and then scores are in A2-A25.
View Replies!
View Related
Searching For Numeric Value Greater Than 3
I have a macro which does a check on certain fields to see if any are greater than the value 3. The cells are in the same column, but goes 33 rows down from the starting point. If it finds it, a messagebox appears to the user. It works fine if it finds values 4 through 9,but does not work with values greater than 9. (I have had data with values of 17 and 22 to test) I first checked the cell types to make sure everything was the same, and it is. Below is my coding, do I have to add something infront of the Value property? 'I have selected the cell where I need to start from For Each i In Range(ActiveCell, ActiveCell.Offset(33, 0)) If i.Value > "3" Then MsgBox ("Pieces per shipment is greater than 3." _ & Chr(13) & "It's value is " & "'" & i.Value & "'" & " found in cell: " & i.Address _ & Chr(13) & ". Profile must go to Finance for review") End If Next i
View Replies!
View Related
Show Rows Greater Than Zero
I have a report with sales statistics from today and yesterday. Then I have a summary sheet that shows the balance between the two. But I'm only interested in seeing the lines with a balance greater than zero. Is there a better way to do it than I've already done? I've attached an example...
View Replies!
View Related
Copy Values Greater Than Zero
I have 2 sheets, i need to transfer every cell value criteria >0 from sheet2 to sheet1. See the code below Sub datatransfer() Dim rngqty As Range Dim nextcell As Range Set nextcell = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0) Sheets("Reqorder").Activate For Each rngqty In Sheets("Reqorder").Range("c4", "n57") Do While rngqty>0 Cells(rngqty.Row, 1).Copy nextcell.PasteSpecial (xlPasteValues) Cells(1, rngqty.Column).Copy nextcell.Offset(0, 1).PasteSpecial (xlPasteValues) rngqty.Copy nextcell.Offset(0, 2).PasteSpecial (xlPasteValues) Loop Next rngqty End Sub
View Replies!
View Related
|
TRACKER
