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

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.
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.
 
Upvote 0
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:
 
Upvote 0
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"))
 
Upvote 0
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.
 
Upvote 0
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),"")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top