Thread: Using multiple if statements with blanks

My data looks like the following

A B C D E
1 T1 T2 T3 T4 Want
2 5 6 9 10 11.11
3 5 6 7 16.67
4 8 11 37.50
5 3 4 4

What I am trying to achieve in column E is the percentage increase between the most current time point, T4 (column D) and the most recent time point which has a non-zero value, provided the current time point has a valid value (isn't blank). Therefore the calculations should come out as:

Line 2: 11.11 because thats the percentage increase between T4 and T3
Line 3: 16.67 because thats the percentage increase between T4 and T2, and T3 is blank
Line 4: 37.50 because thats the percentage increase between T4 and T1 (T2 and T3 blank)
Line 5: Blank, because T4 is blank

My formula to try and achieve this is:

=IF(AND(D2>0,C2>0),D2/C2*100-100,IF(AND(D2>0,B2>0),D2/B2*100-100,IF(AND(D2>0,A2>0),D2/A2*100-100,"")))

The problem I am having is that the blanks in my data A1:D5 aren't being read as blanks, as there is a formula there reading in data from a previous s'sheet. If I delete this formula in my data (A1:D5) for all the blanks, then everything works fine, but I'd rather not have to do this as the data is very large.

Hope someone can come up with a solution to my problem

See Attachment for example

Regards