Ignore Zero Values Between Real Values...

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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.... :oops:
 

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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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.
:pray:
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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

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

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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.

[edit]
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
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
:pray:
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
Joined
Dec 2, 2003
Messages
3,196
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

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
Top