# 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
ADVERTISEMENT
Nov 21, 2013

I want to find greater date among two.

I tried

=If(a1 < B5,1,0)

But problem is my date in B5 itself comes from other formula.

=right(X1,9)

View 6 Replies
View Related
Jun 30, 2009

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 2 Replies
View Related
Apr 18, 2013

I want to count the times that a value in one column has a value of one greater than the value of the corresponding cell in another column.

(For example, A1=1 and B1=2, A2=1 and B2=2, etc.)

I've tried =COUNTIFS(A1:A180, (B1:B180)+1) and =COUNTIFS(A1:A180, "="&(B1:B180)+1) with no luck.

(It gives me a value of zero, but I can tell just by looking at the numbers that there should be at least a few times when this event occurs.)

View 5 Replies
View Related
Mar 3, 2014

My data has a bunch of near 0 figures followed by values I actually need followed by more irrelevant 0's than once again followed by values I need and again irrelevant 0's. Looks like

0.1

0.12

3.2

3.4

0.1

0.3

4.5

4.2

0.3

0.11

but with many more rows and numbers. I'm graphing the start and end values before/after zero's.

The first block of numbers I figured out using

=MATCH(TRUE,INDEX($C8:$C150>1,0),0)+7 I then index from that given row.

The span of relevant values I'm looking for constantly changes, so once my numbers deviate too much I stop, using another index formula.

Now for finding my beginning and end points for the next block of data I've combined using VBA with excel formulas. I can't quite figure this part out. I've created Variables for ranges and then used them to find max and min Values.

Public Sub RunCurrent()

'

' RunCurrent Macro

Dim upEnd As Integer

Dim dnStart As Integer

Dim dnEnd As Integer

[Code] ........

View 2 Replies
View Related
Feb 22, 2007

how I could search for any number great then (x) and replace it a letter

For example I have an excel table with a series of weights.

lbs

6001

4560

6789

2000

5656

8879

1243

I like to replace any number greater or equal to 6001 lbs with the letters SS.

Example:

lbs

SS

4560

SS

2000

5656

SS

1243

Then I'd like do the same thing again but this time replace any number less

then or equal to 6000 lbs with the letters SS-LL .

Example

lbs

SS

SS-LL

SS

SS-LL

SS-LL

SS

SS-LL

View 14 Replies
View Related
Jun 1, 2009

I'm looking for a function which will determine if a target date is greater than or equal to THE JULY FOLLOWING A GIVEN DATE. In the attached spreadsheet the Start Date (B5) would be the GIVEN DATE Target Date is listed in ROW 1. In sentence form the function in cell G2 would be something like, "If G1 >= July following B5, then True.

View 3 Replies
View Related
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
Jan 21, 2009

I get sheets, sometimes 10 records, sometimes 1,000 records. And there is a time column. It's formatted as: hh:mm:ssam (09:12:36am). Now what I need to do is find a way to highlight any record that has a large gap between itself and the record before it. This gap amount can be variable, but for the explanation lets say, I need to highlight any record that has a gap of 5 minutes greater than the time before it.

I've tried a formula I found elsewhere:

=F3-F2> 1/24/60*5

But it just returns "#VALUE!".

I'm completely stumped, and since about last week, 1 of my daily responsibilites will be analysing anywhere from 10 - 50 sheets a day.

The guy before me would open the sheet and manually find it by literally looking from record to record. This was this 1 guy's full time job. From what I'm reading around there are ways to have excel do this for me with a simple formula and conditional formatting.

View 9 Replies
View Related
Jul 29, 2006

I want to find the minimum value of column A if there is anything greater than zero in column B. I tried this formula (simplified): MINIF(B2:B6,">"&0,A2:A6) And it gives me the # NAME? error

View 6 Replies
View Related
Dec 12, 2011

way to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:

A1: 2011/01/04

A2: 2011/02/01

A3: 2011/03/01

A4: 2011/04/01

I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?

I want to do this without using any macros.

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