|
need formulae
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 |
need formulae
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 "Graham" wrote: 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 |
need formulae
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" wrote: 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 |
need formulae
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})) "Jacob Skaria" wrote: 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 "Graham" wrote: 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 |
need formulae
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" wrote: 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" wrote: 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 |
need formulae
Hi,
=IF(A51<0,4,IF(AND(G1-A51=0,G1-A51<=2),3,IF(AND(G1-A512,G1-A51<=3),2,1))) "Graham" wrote: 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" wrote: 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" wrote: 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 |
need formulae
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 "Graham" wrote: 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})) "Jacob Skaria" wrote: 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 "Graham" wrote: 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 |
need formulae
Worked like a dream thanks a mill!!
"Jacob Skaria" wrote: 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 "Graham" wrote: 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})) "Jacob Skaria" wrote: 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 "Graham" wrote: 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 |
need formulae
Cheers..
If this post helps click Yes --------------- Jacob Skaria "Graham" wrote: Worked like a dream thanks a mill!! "Jacob Skaria" wrote: 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 "Graham" wrote: 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})) "Jacob Skaria" wrote: 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 "Graham" wrote: 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 |
need formulae
Hi Jacob
Sorry for being a pain but in the formulae if :(E20-D20)<0 the i get a #### error. Please help. "Jacob Skaria" wrote: Cheers.. If this post helps click Yes --------------- Jacob Skaria "Graham" wrote: Worked like a dream thanks a mill!! "Jacob Skaria" wrote: 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 "Graham" wrote: 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})) "Jacob Skaria" wrote: 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 "Graham" wrote: 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 |
| All times are GMT +1. The time now is 11:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com