need formulae

[Graham]

Hi all I have the following criteria:

planned = 4; 1 hour above planned = 3; 2 hours above planned = 2; >3 hours above planned = 1

I need a formulae to satisfy the above requirements (i have a helper cell
"a51"
E1=PLANNED DATE TIME (dd-mm-yy HH:mm)
F1=ACTUAL DATE TIME
F1-E1=HELPER CELL(A51)
REMEMBER IF A51=O THEN THAT IS 100% PLANNED

THANKX
GEES

 

[Jacob Skaria]

Dear Graham

The below formula do not use the helper cell.

=LOOKUP(VALUE(TEXT(F1-E1,"hh")),{0,1,2,3,4},{"100% Planned",3,2,1,4})

Can you clarify the first condition (>planned =4). I am not sure....But
still you should be able to modify the values from within the lookup and
result array....

If this post helps click Yes

 

[Jacob Skaria]

Dear Graham

The below formula do not use the helper cell.

=LOOKUP(VALUE(TEXT(F1-E1,"hh")),{0,1,2,3,4},{"100% Planned",3,2,1,4})

Can you clarify the first condition (>planned =4). I am not sure....But
still you should be able to modify the values from within the lookup and
result array....

If this post helps click Yes

 

[Eduardo]

Hi,
I assume the first condition is =Planned and that your helper is in G1 in H1
enter
=IF(A51=0,"100%
Complete",IF(G1=A51,4,IF(G1-A51=1,3,IF(G1-A51=2,2,IF(G1-A51>=3,1)))))

 

[Eduardo]

Hi,
I assume the first condition is =Planned and that your helper is in G1 in H1
enter
=IF(A51=0,"100%
Complete",IF(G1=A51,4,IF(G1-A51=1,3,IF(G1-A51=2,2,IF(G1-A51>=3,1)))))

 

[Graham]

Thanks Skaria I have used a formulae like the below but get the wrong result
when (E20-D20) is greater than say 10hrs, please help

=IF(A51<=0,4,LOOKUP(TEXT(E20-D20,"h"),{"0","1","2","3"},{4,3,2,1}))

 

[Graham]

Hi Eduardo
a small correction :IF A51<0 then =4, IF>=0 but <2 then =3,IF >2 but <=3
then=2,IF >3 then =1
This is the situation I have to satisfy, please help

 

[Eduardo]

Hi,
=IF(A51<0,4,IF(AND(G1-A51>=0,G1-A51<=2),3,IF(AND(G1-A51>2,G1-A51<=3),2,1)))

 

[Jacob Skaria]

Graham

Try the below..

Formula without helper cell A51

=IF(VALUE(TEXT(E20-D20,"h"))<=0,4,LOOKUP(VALUE(TEXT(E20-D20,"h")),{0,1,2,3},{4,3,2,1}))

with helper cell

=IF(A51<=0,4,LOOKUP(VALUE(TEXT(E20-D20,"h")),{0,1,2,3},{4,3,2,1}))

If this post helps click Yes

 

[Graham]

Worked like a dream thanks a mill!!

Graham

Try the below..

Formula without helper cell A51

=IF(VALUE(TEXT(E20-D20,"h"))<=0,4,LOOKUP(VALUE(TEXT(E20-D20,"h")),{0,1,2,3},{4,3,2,1}))

with helper cell

=IF(A51<=0,4,LOOKUP(VALUE(TEXT(E20-D20,"h")),{0,1,2,3},{4,3,2,1}))

If this post helps click Yes

 

[Jacob Skaria]

Cheers..

If this post helps click Yes

 

[Graham]

Hi Jacob
Sorry for being a pain but in the formulae if E20-D20)<0 the i get a ####
error.
Please help.