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

Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12
0%	183%	0%	342%	0%	174%	0%	254%	0%	179%	0%	197%

<tbody>
</tbody>

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!

 

[mminchev]

The moderators keep deleting my post with the tables.....

 

[Fluff]

@mminchev
Please note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.
In this case I saw one of your moderated posts & dealt with it & whilst I was doing that the other post was dealt with by another moderator & neither of us realised. Therefore both posts were removed.

 

[shg]

One way -- transposed for convenience:

	A​	B​	C​	D​
1​	​	​	​	​
2​	Week 1	0%​	0%​	C2: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, B$1:B1/(B$1:B1 <> 0))), 0)
3​	Week 2	183%​	0%​
4​	Week 3	0%​	0%​
5​	Week 4	342%​	159%​
6​	Week 5	0%​	0%​
7​	Week 6	174%​	-168%​
8​	Week 7	0%​	0%​
9​	Week 8	254%​	80%​
10​	Week 9	0%​	0%​
11​	Week 10	179%​	-75%​
12​	Week 11	0%​	0%​
13​	Week 12	197%​	18%​

 

[mminchev]

This looks great and I really appreciate it, however I need it to be horizontal, similar to the example I showed earlier. Furthermore, I am not quite familiar with the LOOKUP(9E+307, part of the formula. What would the formula look like if it is horizontal?

One way -- transposed for convenience:

	A​	B​	C​	D​
1​
2​	Week 1	0%​	0%​	C2: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, B$1:B1/(B$1:B1 <> 0))), 0)
3​	Week 2	183%​	0%​
4​	Week 3	0%​	0%​
5​	Week 4	342%​	159%​
6​	Week 5	0%​	0%​
7​	Week 6	174%​	-168%​
8​	Week 7	0%​	0%​
9​	Week 8	254%​	80%​
10​	Week 9	0%​	0%​
11​	Week 10	179%​	-75%​
12​	Week 11	0%​	0%​
13​	Week 12	197%​	18%​

<tbody>
</tbody>

 

[shg]

Just copy and transpose:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

​

Week 1​

Week 2​

Week 3​

Week 4​

Week 5​

Week 6​

Week 7​

Week 8​

Week 9​

Week 10​

Week 11​

Week 12​

2​

​

0%​

183%​

0%​

342%​

0%​

174%​

0%​

254%​

0%​

179%​

0%​

197%​

3​

​

0%​

0%​

0%​

159%​

0%​

-168%​

0%​

80%​

0%​

-75%​

0%​

18%​

B3: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, $A2:A2/($A2:A2 <> 0))), 0)

The calculated array comprises the number values in prior columns if they are <> 0, and a #DIV/0! error otherwise. LOOKUP only matches data of like type of the lookup value (here, a number -- an enormous number). The LOOKUP function ASSUMES the lookup array is sorted, so it does a binary search. And since there is no number larger than the (enormous) lookup value, it returns the last number in the array, which is the one you want.

 

[mminchev]

I really appreciate your help, you are a life saver! And I learned something new today, again thank you so much!

Just copy and transpose:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Week 1​

Week 2​

Week 3​

Week 4​

Week 5​

Week 6​

Week 7​

Week 8​

Week 9​

Week 10​

Week 11​

Week 12​

2​

0%​

183%​

0%​

342%​

0%​

174%​

0%​

254%​

0%​

179%​

0%​

197%​

3​

0%​

0%​

0%​

159%​

0%​

-168%​

0%​

80%​

0%​

-75%​

0%​

18%​

B3: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, $A2:A2/($A2:A2 <> 0))), 0)

<tbody>
</tbody>

The calculated array comprises the number values in prior columns if they are <> 0, and a #DIV/0! error otherwise. LOOKUP only matches data of like type of the lookup value (here, a number -- an enormous number). The LOOKUP function ASSUMES the lookup array is sorted, so it does a binary search. And since there is no number larger than the (enormous) lookup value, it returns the last number in the array, which is the one you want.

 

[shg]

You're welcome.