Move If Greater Than Or Less Than?

Dec 6, 2013

I am trying to format a column of data. What I would like to do is move the contents of the cell based on the number in the cell.

I would like to move the content if it is greater than 5, but less than 70. If the content is between these, move it to a given location.

Is this an if/then? Ive got a few programming books here but not sure how to relate what I want to do to an example in the books.

View 4 Replies


ADVERTISEMENT

Record Greater Than 50 In A Cell So It Reads As Greater Than 50

Aug 25, 2009

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 4 Replies View Related

Is There A Way To Stick A Few Row Of Cells Together So That When I Move One They All Move

Jul 7, 2009

is there a way to stick a few row of cells together so that when i move one they all move.. i have some rows that when i sort them i want them to move together with the other ones..

View 9 Replies View Related

If Value Greater Than

Jun 11, 2009

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 3 Replies View Related

Greater Than / Less Than

Dec 8, 2008

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

If A1 Is Greater Than 25

Jan 5, 2013

This is what I am tyring to do. I would like B7 to show $60 once A1 is greater then 25.

View 3 Replies View Related

IF, THEN, Greater Than, Less Than

Nov 14, 2006

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 9 Replies View Related

Greater Than Zero

Feb 5, 2007

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

Add Value If Greater Than

Aug 7, 2007

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 9 Replies View Related

Average IF Value Greater Than 0?

Dec 9, 2013

Here is my code as it is right now (With the correct range, and yes its a messy range :

[Code] ......

That is my range and thats the range I have to use (The stats are spread out in those rows. How I can get this to disregard 0's? Right now it seems to fail on the third rangevalue (For example in the range above it fails and highlights Y88), but if i remove that value it does the same on the next one instead, always the third.

I have tried =AVERAGEIFS(Range,">0") versions and also ones with /COUNTIF after and others but cant get this to work.

View 4 Replies View Related

Date Less Than Greater Than

Jan 30, 2008

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 12 Replies View Related

Count If Greater Than But Less Than

Aug 10, 2009

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 2 Replies View Related

Formula For Greater , Less Than

Sep 17, 2009

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 3 Replies View Related

Minimum Greater Than Zero

Jan 25, 2005

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 10 Replies View Related

Greater Than And Less Than In One IF Statement?

Nov 26, 2002

Is it possible to have the condition IF(H9>E9>G9,E9-G9,0) so that the calculation is only done if E9 is between H9 and G9?

View 5 Replies View Related

Countif And Less Than Or Greater Than?

Nov 28, 2011

I am trying to use Countif on a range of cells some of which have a value of "< 5" (ie less than 5), but for those cells it returns a sum of 0 (zero).

View 2 Replies View Related

Greater Than Or Less Than In Same Formula

Jan 9, 2007

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 9 Replies View Related

Formula Greater Than/Less Than

Mar 15, 2007

I need a formula, If the number is greater than 1 and less then 30, it returns Yes, if not, then no.

View 9 Replies View Related

Set All Values Greater Than X To Y

Jun 3, 2008

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.

View 9 Replies View Related

SUMIF: Value Is Greater Than Zero

Oct 20, 2009

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 9 Replies View Related

Greater Than Or Less Than (Plus Or Minus)

Jul 19, 2006

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 4 Replies View Related

Multiple Greater Than Less Than

Feb 25, 2007

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 3 Replies View Related

Small Greater Than Zero

Mar 9, 2007

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 2 Replies View Related

Find First Value Greater Than Zero

Mar 10, 2007

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.

View 4 Replies View Related

Greater Than But Less Than Formula

Jun 28, 2007

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 5 Replies View Related

Sum Values Greater Than Zero

Sep 12, 2007

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 2 Replies View Related

Sum Only If All Cells Are Greater Than Zero

Aug 6, 2008

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 3 Replies View Related

Round To Nearest 0.99 If Value Is Greater Than 0.95 Or Less Than 0.05

Dec 12, 2013

I am looking for a formula which will allow a value to round up or down to the nearest x.99 if the value is greater than x.95 or less x.05 otherwise remain the same. i.e if the value is 0.96 it should read 0.99 and if the value is 1.04 it should read 0.99. however if the value 1.05 it should read 1.05.

View 9 Replies View Related

Minimum In Range Greater Than Zero

Dec 15, 2006

I want to find the minimum time value within a range of cells, excluding 0:00. Currently,

VB:
=MIN(BL5:CP5)

returns 0:00 if it exists in any of the cells.

View 3 Replies View Related

Calculating Time If Value Is Less Than Or Greater Than?

Mar 30, 2014

I'm having a DICKENS of a time on this one. To explain, I have 3 columns where time is entered: a 'Time of Call', 'Time of Arrival', and 'Response Time'. The 'Response Time' cell autcalculates by simply subtracting the 'Time of Arrival' form the 'Time of Call' and using the difference to show the response time. To make time entry into the 'Time of Call' and 'Time of Arrival' simplier for my co-workers, I formatted those cells as 00:00 so that anytime you enter a number it will convert it to a time format (i.e., if you enter in the numbers 745, it will show as 7:45 in the cell). Because of this, when the response time is configured, it does it in hundreds instead of time format. So, if you enter in 7:45 as your time of call and 8:15 as your time of arrival, it shows 70 minutes as a response time because it sees the 7:45 as a whole number...745. Therefore, 815-745 DOES equal 70, but...that's not what I'm looking for. I want it too configure this as time...not as whole numbers.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved