EXCEL Forumulas please
- 状态: Closed
- 奖金: $20
- 参赛作品已收到: 4
- 获胜者: bhavdipporiya
竞赛简介
We previously developed the below formula which produced the banded decimals in the final column below (also attached):
=IF(G3="","",IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440=0,0,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=17,0.25,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=22,0.33,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=35,0.5,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=50,0.75,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=65,1,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440>65,TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*24))))))))
28 minutes 0.5
41 minutes 0.75
53 minutes 1
22 minutes 0.33
We would now like the formula to produce the following banded decimals.
0 to 22 minutes – 0.25
23 – 37 minutes - 0.5
38 – 52 minutes - 0.75
53 to 67 minutes - 1
above 67 minutes - no amendment - just convert the minutes to decimals (e.g. 90 minutes would be 1.5)
ALSO.
We would like another formula creating which produces time in decimels but rounded up to the nearest 5 minutes. Example:
1 hour = 1 hour = 1
30 mins = 30 mins = 0.5
32 mins = FORMULA ROUNDS UP TO 35 mins (nearest 5 mins) = 0.583
31 mins = FORMULA ROUNDS UP TO 35 mins (nearest 5 mins) = 0.583
44 mins = FORMULA ROUNDS UP TO 45 mins (nearest 5 mins) = 0.750
Thanks
您还可能感兴趣的技能
雇主反馈
“Very good thanks”
uksikh, United Kingdom.
此竞赛的顶尖作品
-
bhavdipporiya India
-
mtdevil369 Bangladesh
-
soufianesouhail Morocco
-
umairabdillah12 Indonesia