IF Statement that skips 0 and looks at the next real number

[mminchev]

I am trying to create and IF statement that basically will look at values in different cells in the same row and would give me the difference between each cell, however is there is a 0 in one cell it would look at the next available number in a cell that is not a zero and will give me that difference:

For Example:

If this is my row of numbers I would need to find the % difference between only the percent and skip the zeros


[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 1
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 2
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 3
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 4
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 5
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 6
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 7
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 8
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 9
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 10
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 11
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Week 12
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]0%
[/TD]
[TD="class: xl66, bgcolor: transparent"]183%
[/TD]
[TD="class: xl66, bgcolor: transparent"]0%
[/TD]
[TD="class: xl66, bgcolor: transparent"]342%
[/TD]
[TD="class: xl66, bgcolor: transparent"]0%
[/TD]
[TD="class: xl66, bgcolor: transparent"]174%
[/TD]
[TD="class: xl66, bgcolor: transparent"]0%
[/TD]
[TD="class: xl66, bgcolor: transparent"]254%
[/TD]
[TD="class: xl66, bgcolor: transparent"]0%
[/TD]
[TD="class: xl66, bgcolor: transparent"]179%
[/TD]
[TD="class: xl66, bgcolor: transparent"]0%
[/TD]
[TD="class: xl66, bgcolor: transparent"]197%
[/TD]
[/TR]
</tbody>[/TABLE]


So this means my IF statement would skip Week 1 and look at the difference of Week 4 - Week 2, how can I build and IF Statement that can do that?

I really appreciate the help, this is very time sensitive, It is probably simple, but I am drawing a blank!

 

[shg]

What are your expected results for that data?

 

[mminchev]

I am trying to find the difference between the weeks but instead of going in order Week 2 - Week 1, I want to skip the weeks that show 0% and go to the next real number, for example Week 4 - Week 2 and tell the If Statement to skip all the 0%

 

[mminchev]

Basically something like this:

IF(Week 1=0, then skip week 1 and look at the next number that is not zero and then subtract it from the next higher week number that is not a zero)

 

[shg]

So you don't know the results for you want for your example?

 

[mminchev]

Im sorry Im confused by your question

So you don't know the results for you want for your example?

 

[dreid1011]

Shg is asking for you to show what the output should look like based on your example provided. What should the ACTUAL resulting numbers be?

 

[mminchev]

Lets say Week 4 - Week 2 or 342-183=159

So I am trying to make the If Statement skip all the zeros and ignore them and just look at the numbers and get their difference.

So far I have something like this:

=IF(J4=0,"",IF(K4=0,"",IF(L4=0,"",IF(M4=0,"",IF(N4=0,"",IF(O4=0,"",IF(P4=0,"",IF(Q4=0,"",IF(R4=0,"",IF(S4=0,"",IF(T4=0,"",K4-J4)))))))))))

but I don't know how to put in there to just subtract the numbers and not every cell by the other, how to skip the cells with zeros in them and go and look at the next number

I don't know if I am making any sense, Im sorry, Ive just drawn a blank

Shg is asking for you to show what the output should look like based on your example provided. What should the ACTUAL resulting numbers be?

 

[dreid1011]

Lets say Week 4 - Week 2 or 342-183=159

This. You're on the right track here as far as showing the expected results. Can you show more for the entire row you have in post #1 ?

 

[mminchev]

What the results should look like after the If Statement:

[TABLE="width: 672"]
<colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" span="12"> <tbody>[TR]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 1[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 2[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 3[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 4[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 5[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 6[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 7[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 8[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 9[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 10[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 11[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]Week 12[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]183%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]342%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]174%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]254%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]179%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]197%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]RESULTS:[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Week 4 - Week 2[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Week 6 - Week 4[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Week 8 - Week 6[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Week 10 - Week 8[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Week 12 - Week 10[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]159%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]-168%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]79%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]-75%[/TD]
[TD="class: xl66, bgcolor: transparent"]0%[/TD]
[TD="class: xl66, bgcolor: transparent"]19%[/TD]
[/TR]
</tbody>[/TABLE]

This. You're on the right track here as far as showing the expected results. Can you show more for the entire row you have in post #1 ?