EXCEL Forumulas please

• ステータス: Closed
• 賞金: $20
• 受け取ったエントリー: 4
• 優勝者: bhavdipporiya

Winner

コンテスト概要

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

推奨スキル

• データ入力
• 情報処理
• エクセル