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

M

mminchev

New Member
Joined
Feb 16, 2018
Messages
23
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!
 
Sort by date Sort by votes

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
@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.
 
Last edited:
Upvote 0
One way -- transposed for convenience:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]Week 1[/td][td]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td]C2: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, B$1:B1/(B$1:B1 <> 0))), 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]Week 2[/td][td]
183%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]Week 3[/td][td]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]Week 4[/td][td]
342%​
[/td][td="bgcolor:#E5E5E5"]
159%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]Week 5[/td][td]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]Week 6[/td][td]
174%​
[/td][td="bgcolor:#E5E5E5"]
-168%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]Week 7[/td][td]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]Week 8[/td][td]
254%​
[/td][td="bgcolor:#E5E5E5"]
80%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]Week 9[/td][td]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td]Week 10[/td][td]
179%​
[/td][td="bgcolor:#E5E5E5"]
-75%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td]Week 11[/td][td]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td]Week 12[/td][td]
197%​
[/td][td="bgcolor:#E5E5E5"]
18%​
[/td][td][/td][/tr]
[/table]
 
Upvote 0
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?



shg said:
One way -- transposed for convenience:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
C​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
D​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
1​
[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
2​
[/TD]
[TD]Week 1
[/TD]
[TD]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD]C2: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, B$1:B1/(B$1:B1 <> 0))), 0)
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
3​
[/TD]
[TD]Week 2
[/TD]
[TD]
183%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
4​
[/TD]
[TD]Week 3
[/TD]
[TD]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
5​
[/TD]
[TD]Week 4
[/TD]
[TD]
342%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
159%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
6​
[/TD]
[TD]Week 5
[/TD]
[TD]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
7​
[/TD]
[TD]Week 6
[/TD]
[TD]
174%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
-168%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
8​
[/TD]
[TD]Week 7
[/TD]
[TD]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
9​
[/TD]
[TD]Week 8
[/TD]
[TD]
254%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
80%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
10​
[/TD]
[TD]Week 9
[/TD]
[TD]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
11​
[/TD]
[TD]Week 10
[/TD]
[TD]
179%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
-75%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
12​
[/TD]
[TD]Week 11
[/TD]
[TD]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
13​
[/TD]
[TD]Week 12
[/TD]
[TD]
197%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
18%​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Just copy and transpose:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][td="bgcolor:#C0C0C0"]
J​
[/td][td="bgcolor:#C0C0C0"]
K​
[/td][td="bgcolor:#C0C0C0"]
L​
[/td][td="bgcolor:#C0C0C0"]
M​
[/td][td="bgcolor:#C0C0C0"]
N​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
Week 1​
[/td][td="bgcolor:#F3F3F3"]
Week 2​
[/td][td="bgcolor:#F3F3F3"]
Week 3​
[/td][td="bgcolor:#F3F3F3"]
Week 4​
[/td][td="bgcolor:#F3F3F3"]
Week 5​
[/td][td="bgcolor:#F3F3F3"]
Week 6​
[/td][td="bgcolor:#F3F3F3"]
Week 7​
[/td][td="bgcolor:#F3F3F3"]
Week 8​
[/td][td="bgcolor:#F3F3F3"]
Week 9​
[/td][td="bgcolor:#F3F3F3"]
Week 10​
[/td][td="bgcolor:#F3F3F3"]
Week 11​
[/td][td="bgcolor:#F3F3F3"]
Week 12​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td]
0%​
[/td][td]
183%​
[/td][td]
0%​
[/td][td]
342%​
[/td][td]
0%​
[/td][td]
174%​
[/td][td]
0%​
[/td][td]
254%​
[/td][td]
0%​
[/td][td]
179%​
[/td][td]
0%​
[/td][td]
197%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
159%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
-168%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
80%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
-75%​
[/td][td="bgcolor:#E5E5E5"]
0%​
[/td][td="bgcolor:#E5E5E5"]
18%​
[/td][td]B3: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, $A2:A2/($A2:A2 <> 0))), 0)[/td][/tr]
[/table]

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.
 
Last edited:
Upvote 0
Solution
I really appreciate your help, you are a life saver! And I learned something new today, again thank you so much!






shg said:
Just copy and transpose:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
C​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
D​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
E​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
F​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
G​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
H​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
I​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
J​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
K​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
L​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
M​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
N​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
1​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 1​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 4​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 5​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 6​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 7​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 8​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 9​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 10​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 11​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]
Week 12​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "][/TD]
[TD]
0%​
[/TD]
[TD]
183%​
[/TD]
[TD]
0%​
[/TD]
[TD]
342%​
[/TD]
[TD]
0%​
[/TD]
[TD]
174%​
[/TD]
[TD]
0%​
[/TD]
[TD]
254%​
[/TD]
[TD]
0%​
[/TD]
[TD]
179%​
[/TD]
[TD]
0%​
[/TD]
[TD]
197%​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]
3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
159%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
-168%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
80%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
-75%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
0%​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] "]
18%​
[/TD]
[TD]B3: =IFERROR(IF(B2=0, 0, B2 - LOOKUP(9E+307, $A2:A2/($A2:A2 <> 0))), 0)
[/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Skip Row When Column Word Meet
2
Replies
14
Views
389
Peter_SSs
Peter_SSs
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
702
bh24524
B
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
532
mumps
mumps
J
  • Solved
Calculate how long it would take to reduce a backlog to 0 based on weekly additions/subtractions
Replies
1
Views
477
Snakehips
Snakehips
J
  • Question Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J

Forum statistics

Threads
1,224,828
Messages
6,181,215
Members
453,024
Latest member
Wingit77

Share this page

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