Tracker
HOME    TRACKER    Excel

# 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.

Related Forum Messages:
Greater Than Or Less Than In Same Formula
I have the following values that I need to perform a calculation on, but I am not sure how.

A1 = -.98
A2 = .98
A3 = 1.0

I would like the results in colmun B to be:

B1 = 1.02
B2 = .98
B3 = 1.0

I would like to achieve this with a formula that I could fill down.

Formula For Greater , Less Than
I have this table with min and max amounts that requires a fixed amount when when the condition is met. How do I write a formula for this. If result is >\$0 but < \$100 = \$15 and so on. I canlt get it right..........

Greater Than But Less Than Formula
Need the formula to find any number from a range under this format 00:00:00, Greater than 04:00:00 but Less Than 12:00:00.

Formula Not Calculating When Greater Then Zero
Currently I am using the following formula ....

Greater Than, Smaller Than Formula
When grading children's test scores I want to apply letters and numerals to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help with a formula.

Greater Than Less Than Formula For An Input
I need to display two separate values from a given input, but not exceed a specified number for that cell. I have this so far except for the maximum number that can be displayed; Example for what I want below ....

Greater Than And Less Than Or Equal To Formula
I need a formula that looks at the total in H40 and if the number is between 32 and 40 I need it to return the number then if the number exceds forty I need to multiply the overage by 1.5 and add it to the 8 for a total of 11.

I think it would be something like:

SUMIf(H40>"32",

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.

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

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.

Formula: Change Greater Value Is Inserted In Cell
I have this formula
=IF('Senate 1'!K10="",'Senate 1'!K\$8,IF(S10>'Senate 1'!K10,S10,""))

what am hoping i can get it to perform is have S10 replace it self each time a greater value is inserted in Cell S10.

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.

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

'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.

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: ....

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.

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.

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.

IF OR Formula: If Cell A1 Or Cell B1 Value Is Greater Then 175 Then "on", If Not Then "off"
I need to formula that does the following. If Cell A1 or cell B1 value is greater then 175 then "on", if not then "off"

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.

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...

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.

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?

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.

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))))

Find First Value Greater Than Zero
I have a list of numbers like this.
0,0,0,12,3,1,6,9

I need to find the first greater than zero number and then add up that number and the following two numbers. In the case above the answer would be 16.

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,...

Small Greater Than Zero
is there any way of using the =SMALL function to rank only numbers above zero so that the zeros don't keep showing up as the smallest figures?

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"

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.

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

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?

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

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

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"...

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.

Conditional Formatting: Greater Than 50%
I would like to use conditional formatting to say that if the value in A5 is greater than 50% of the sum of B5 and C5 then turn green.

Set All Values Greater Than X To Y
I have a huge sheet with data. I want to fix all values exceeding e.g. 2000 to 2000.

for instance:

400 --> 400
1600 --> 1600
2300 --> 2000
700 --> 700
3100 --> 2000

etc.

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...

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.

Count Values Greater Than Zero
I want to look to a range and if there is a value greater than zero I want to count it. I keep going round in circles trying to do this and now I give up.

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.

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.

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)))

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.

Minimum In Range Greater Than Zero
I want to find the minimum time value within a range of cells, excluding 0:00. Currently, =MIN(BL5:CP5). returns 0:00 if it exists in any of the cells.

Sum Only If All Cells Are Greater Than Zero
I have three cells with data: 23.40, 12.34, and 0

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

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

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...

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