Nested IF that ignores blank cells

si.mon · May 22, 2018

Hello,

I'm in need of some help with this one and am ready to give up!!

I am trying to determine progress status of certain deliverables at a given date.

If there is a date in cell M to T, I would like column AP to show the value of that progress dependent on the date entered in cell AP1 . So far, I get either 0 or 1!!

Current best effort is:

=IF(OR(T3="",S3="",R3="",Q3="",P3="",O3="",N3="",M3=""),"",IF($AP$1<T3,1,IF($AP$1<S3,0.925,IF($AP$1<R3,0.75,IF($AP$1<Q3,0.7,IF($AP$1<P3,0.6,IF($AP$1<O3,0.45,IF($AP$1<N3,0.25,IF($AP$1<M3,0.05,0)))))))))

Many thanks,

Simon

Pecoflyer · May 22, 2018

Hi
posting a sheet instead of a picture would make life easier for us; (click Go advanced - Manage attachments). Thank you

NBVC · May 23, 2018

You might try replacing the first part like this:

=IF(SUM(M3:T3)=0,"",IF($AP$1<T3,1,IF($AP$1<S3,0.925,IF($AP$1<R3,0.75,IF($AP$1<Q3,0.7,IF($AP$1<P3,0.6,IF($AP$1<O3,0.45,IF($AP$1<N3,0.25,IF($AP$1<M3,0.05,0)))))))))

Nested IF that ignores blank cells

si.mon

New member

Pecoflyer

Admin Alumnus

NBVC

Super Moderator