EXCEL Forumulas please
- Status: Closed
- Hadiah: $20
- Entri Diterima: 4
- Pemenang: bhavdipporiya
Deskripsi Kontes
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
Keahlian yang Disarankan
Umpan balik Pemberi kerja
“Very good thanks”
uksikh, United Kingdom.
Entri teratas dari kontes ini
-
bhavdipporiya India
-
mtdevil369 Bangladesh
-
soufianesouhail Morocco
-
umairabdillah12 Indonesia
Papan Klarifikasi Publik
Bagaimana untuk memulai sebuah kontes
-
Buat Kontes Anda Cepat dan mudah
-
Dapatkan Jutaan Entri Dari seluruh dunia
-
Pilih entri terbaik Unggah file - Mudah!