# 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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Zack Barresse

##### MrExcel MVP

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.

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

##### 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
5
Views
404
Replies
1
Views
333
Replies
2
Views
119
Replies
7
Views
158
Replies
3
Views
511

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,160
Messages
5,768,537
Members
425,480
Latest member
br400821

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

### Which adblocker are you using?

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

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