nested ifs not enough
 

Hi everyone

I have results in random days. This next formula works perfectly when i have
a maximum "hole" of 7 days. The problem is when i have gaps of more than 7
days.

E157=IF(D157=0;"";IF(D157-D156<D157;D157-D156;IF(D157-D155<D157;D157-D155;SE(D157-D154<D157;D157-D154;IF(D157-D153<D157;D157-D153;IF(D157-D152<D157;D157-D152;IF(D157-D151<D157;D157-D151;IF(D157-D150<D157;D157-D150;"ERROR"))))))))

The D column is the readings column. The first if tells me that if i dont
have any reading then no result will show up. However, if that´s not the
case, i want in column E current reading minus last reading (whenever that
may be).

I tried understanding VLOOKUP but it doesnt seem to help me.

thanks a lot

nested ifs not enough
 

If I understand you correctly try:

=IF(D157=0;"";D157-LOOKUP(E1+307;$D$1:D156))

Ther lookup will find the last value in Column D before D157

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Vincent" wrote in message
...
Hi everyone

I have results in random days. This next formula works perfectly when i
have
a maximum "hole" of 7 days. The problem is when i have gaps of more than
7
days.

E157=IF(D157=0;"";IF(D157-D156<D157;D157-D156;IF(D157-D155<D157;D157-D155;SE(D157-D154<D157;D157-D154;IF(D157-D153<D157;D157-D153;IF(D157-D152<D157;D157-D152;IF(D157-D151<D157;D157-D151;IF(D157-D150<D157;D157-D150;"ERROR"))))))))

The D column is the readings column. The first if tells me that if i dont
have any reading then no result will show up. However, if that´s not the
case, i want in column E current reading minus last reading (whenever that
may be).

I tried understanding VLOOKUP but it doesnt seem to help me.

thanks a lot