Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  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.

View Complete Thread with Replies

Sponsored Links:

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.

View Replies!   View Related
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..........

View Replies!   View Related
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.

View Replies!   View Related
Formula Not Calculating When Greater Then Zero
Currently I am using the following formula ....

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

View Replies!   View Related
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 ....

View Replies!   View Related
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:


View Replies!   View Related
Minimum Formula Greater Than To Zero
I have a range of weekly sub totals that get entered each week for the year.

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:





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

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

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


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
Find First Value Greater Than Zero
I have a list of numbers like this.

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.

View Replies!   View Related
Greater Than Or Less Than (Plus Or Minus)
Here is my formula.


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

View Replies!   View Related
Sum Values Greater Than Zero
In columns A and B, I have a list of time values. Format: [h]:mm:ss. Column C is B-A. Last row of Column C is the sum of all time values of column C. Sometimes A>B, or either A or B is blank. In such casescorresponding C value becomes ##### (negative date and time) And hence sum of all C values which is in last row of C becomes ####.

View Replies!   View Related
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?

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
Run Macro If Cell Value Greater Than 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

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
Sum Values In Range Greater Than Zero
I am trying the sumproduct funtion,but getting #value error.I only want to sum up values greater than zero,omiting blanks,formulas

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

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

View Replies!   View Related
Textbox Greater Than 255 Characters
Not sure if this is a simple question or not - is it possible to pass the contents of a userform textbox (which exceeds 255 characters ) to a worksheet cell? It seems to truncate when I try to do so.

View Replies!   View Related
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 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
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.

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
Sum Data If Dates Greater Than Value
I want to sum data for our fiscal year (Oct08 - Sep09). I want to put in a formula in Q5 that says to sum the data in B5 through N5 if the dates in
B4 through N4 are Oct08 or greater.

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=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

View Replies!   View Related
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.

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.


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
Copyright 2005-08, All rights reserved