# Ignore Zero Values Between Real Values...

#### BuddieB

##### Board Regular
I am trying to write a formula that will read only the data values in a column and ignore the zero values. Is this possible with a formula or is VBA the only way to do it. Here is a sample of my sheet:
Book1
ABCDE
1DateDATAAVERAGESettlement (ft)NetChange (ft.)
211/10/200375.0775.25-0.18
312/19/200375.0575.24-0.19-0.0125
41/2/200400.000.00
51/9/200400.000.00
61/30/200475.0375.24-0.21-0.02
TESTING

I am trying to calculate the net change between dates that have actual values entered. The values are read in from a database sheet so sometimes there will not be data for a given date, hence the zero values. I am using a VLOOKUP to get all the values for the DATA and AVERAGE columns, if that helps any.

I've been wrestling with this one for a while now....

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Zack Barresse

##### MrExcel MVP
how about an IF statement.

if you just don't like to see 0's, how about formatting as accounting (w/ sign as none). it will put a "-" for zero.

#### Aladin Akyurek

##### MrExcel MVP
Is the value in E6 unwanted?

#### BuddieB

##### Board Regular
I am really looking for a formula in E6 that will give me

=D6-D3 if cells D4 and D5 contain zero values

if only D5 contains zero then the formula for E6 would be =D6-D4

Does that help make it clearer?

I am trying to create a formula that can be dragged down continuously independent of the number of zeros between the actual data points.
ray:

#### Cbrine

##### Well-known Member
BuddieB said:
I am really looking for a formula in E6 that will give me

=D6-D3 if cells D4 and D5 contain zero values

if only D5 contains zero then the formula for E6 would be =D6-D4

Does that help make it clearer?

I am trying to create a formula that can be dragged down continuously independent of the number of zeros between the actual data points.
ray:

=if(and(D4=0,D5=0),D6-D3,if(D5=0,D6-D4,"Doesn't meet and conditions"))

#### BuddieB

##### Board Regular
Cbrine said:
=if(and(D4=0,D5=0),D6-D3,if(D5=0,D6-D4,"Doesn't meet and conditions"))

yes I know that this will handle it when there are 2 values between, but I dont know how to make this dynamic.

The number of zeros between values can vary from none to 5 or more... this seems to be my sticking point.

Thanks for all the help thus far in working through this.

Alladin: the value in E6 is what I am trying to create a formula for. That is the value I will need in other calculations.

#### Aladin Akyurek

##### MrExcel MVP
BuddieB said:
I am really looking for a formula in E6 that will give me

=D6-D3 if cells D4 and D5 contain zero values

if only D5 contains zero then the formula for E6 would be =D6-D4

Does that help make it clearer?

I am trying to create a formula that can be dragged down continuously independent of the number of zeros between the actual data points.
ray:
Book5
ABCDE
1DateDATAAVERAGESettlement(ft)NetChange(ft)
23793575.0775.25-0.18
33797475.0575.24-0.19-0.01
43798800
53799500
63801675.0375.24-0.21-0.02
Sheet1

Formulas...

D2:

=IF(N(B2)*N(C2),B2-C2,"")

E3:

=IF(N(D3),D3-LOOKUP(9.99999999999999E+307,\$D\$2:D2),"")

#### Cbrine

##### Well-known Member
Sorry BuddyB, I only read the single post above, not the rest of them.
PS-If I had noticed Aladin was in the post, I wouldn't have even bothered trying to come up with a better solution.

#### BuddieB

##### Board Regular
As usual Alladin, you come through in the clutch. Many thanks.

Replies
2
Views
322
Replies
3
Views
492
Replies
1
Views
266
Replies
14
Views
728
Replies
12
Views
297

### Forum statistics

Threads
1,186,162
Messages
5,956,283
Members
438,245
Latest member
bhavdip_mangukiya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back