{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Rounding"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Rounding","index":0,"n_rows":31,"n_cols":5,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["Rounding is Fun","Input","Output","FormulaLocal","FormulaWOT"]
["Round a number to an integer",{"$f":"PI()","v":3.1415926535897931},{"$f":"ROUND(B2,0)","v":3},{"$f":"sbGetCell(C2,6)","v":"=RUNDEN(B2;0)"},{"$f":"sbGetCell(C2,41)","v":"=ROUND(B2,0)"}]
["Round a number to two digits",{"$f":"PI()","v":3.1415926535897931},{"$f":"ROUND(B3,2)","v":3.14},{"$f":"sbGetCell(C3,6)","v":"=RUNDEN(B3;2)"},{"$f":"sbGetCell(C3,41)","v":"=ROUND(B3,2)"}]
["Round a number to next thousand",2020,{"$f":"ROUND(B4,-3)","v":2000},{"$f":"sbGetCell(C4,6)","v":"=RUNDEN(B4;-3)"},{"$f":"sbGetCell(C4,41)","v":"=ROUND(B4,-3)"}]
["Round a number to 50p",5.6719999999999997,{"$f":"ROUND(B5 * 2,0) / 2","v":5.5},{"$f":"sbGetCell(C5,6)","v":"=RUNDEN(B5 * 2;0) / 2"},{"$f":"sbGetCell(C5,41)","v":"=ROUND(B5 * 2,0) / 2"}]
["Round down if 35p or less, else round up",2.36,{"$f":"CEILING(B6 - 0.35,1)","v":3},{"$f":"sbGetCell(C6,6)","v":"=OBERGRENZE(B6 - 0,35;1)"},{"$f":"sbGetCell(C6,41)","v":"=CEILING(B6 - 0.35,1)"}]
["Round to 1/64",{"$f":"2.1718","v":2.1718000000000002},{"$f":"ROUND(B7 * 64,0) / 64","v":2.171875},{"$f":"sbGetCell(C7,6)","v":"=RUNDEN(B7 * 64;0) / 64"},{"$f":"sbGetCell(C7,41)","v":"=ROUND(B7 * 64,0) / 64"}]
["Show bond price in US T-Note format (see Bloomberg © for example)",100.578125,{"$f":"INT(B8) & REPT(\"-\" & MOD(B8,1) * 32, SIGN(MOD(B8,1)))","v":"100-18,5"},{"$f":"sbGetCell(C8,6)","v":"=GANZZAHL(B8) & WIEDERHOLEN(\"-\" & REST(B8;1) * 32; VORZEICHEN(REST(B8;1)))"},{"$f":"sbGetCell(C8,41)","v":"=INT(B8) & REPT(\"-\" & MOD(B8,1) * 32, SIGN(MOD(B8,1)))"}]
["Show bond price in US T-Note format (see Bloomberg © for example) 2nd version",100.578125,{"$f":"INT(B9) & REPT(\"-\" & INT(MOD(B9,1) * 32) & REPT(CHAR(187 + INT((MOD(B9,1) - INT(MOD(B9,1) * 32) / 32) * 128)), INT((MOD(B9,1) - INT(MOD(B9,1) * 32) / 32) * 128) > 0), INT(MOD(B9,1) * 128) > 0)","v":"100-18½"},{"$f":"sbGetCell(C9,6)","v":"=GANZZAHL(B9) & WIEDERHOLEN(\"-\" & GANZZAHL(REST(B9;1) * 32) & WIEDERHOLEN(ZEICHEN(187 + GANZZAHL((REST(B9;1) - GANZZAHL(REST(B9;1) * 32) / 32) * 128)); GANZZAHL((REST(B9;1) - GANZZAHL(REST(B9;1) * 32) / 32) * 128) > 0); GANZZAHL(REST(B9;1) * 128) > 0)"},{"$f":"sbGetCell(C9,41)","v":"=INT(B9) & REPT(\"-\" & INT(MOD(B9,1) * 32) & REPT(CHAR(187 + INT((MOD(B9,1) - INT(MOD(B9,1) * 32) / 32) * 128)), INT((MOD(B9,1) - INT(MOD(B9,1) * 32) / 32) * 128) > 0), INT(MOD(B9,1) * 128) > 0)"}]
["Round a date time to an hour","2010-04-26T16:37:12",{"$f":"ROUND(B10 * 24,0) / 24","v":"2010-04-26T17:00:00"},{"$f":"sbGetCell(C10,6)","v":"=RUNDEN(B10 * 24;0) / 24"},{"$f":"sbGetCell(C10,41)","v":"=ROUND(B10 * 24,0) / 24"}]
["Round a date time to next 10 min point","2010-04-26T16:37:12",{"$f":"ROUND(B11 * 24 * 6,0) / 24 / 6","v":"2010-04-26T16:40:00"},{"$f":"sbGetCell(C11,6)","v":"=RUNDEN(B11 * 24 * 6;0) / 24 / 6"},{"$f":"sbGetCell(C11,41)","v":"=ROUND(B11 * 24 * 6,0) / 24 / 6"}]
["Round to 1/8 but show 1/4, 1/2, 3/4 instead of 2/8, 4/8, 6/8",-2.5,{"$f":"CHOOSE(2 + SIGN(B12),\"-\",\"0\",\"\") & IF(INT(ABS(B12)) <> 0,INT(ABS(B12)) & \" \",\"\") & CHOOSE(1 + ROUND((ABS(B12) - INT(ABS(B12))) * 8,0),\"\", \"1/8\",\"1/4\",\"3/8\",\"1/2\",\"5/8\",\"3/4\",\"7/8\")","v":"-2 1/2"},{"$f":"sbGetCell(C12,6)","v":"=WAHL(2 + VORZEICHEN(B12);\"-\";\"0\";\"\") & WENN(GANZZAHL(ABS(B12)) <> 0;GANZZAHL(ABS(B12)) & \" \";\"\") & WAHL(1 + RUNDEN((ABS(B12) - GANZZAHL(ABS(B12))) * 8;0);\"\"; \"1/8\";\"1/4\";\"3/8\";\"1/2\";\"5/8\";\"3/4\";\"7/8\")"},{"$f":"sbGetCell(C12,41)","v":"=CHOOSE(2 + SIGN(B12),\"-\",\"0\",\"\") & IF(INT(ABS(B12)) <> 0,INT(ABS(B12)) & \" \",\"\") & CHOOSE(1 + ROUND((ABS(B12) - INT(ABS(B12))) * 8,0),\"\", \"1/8\",\"1/4\",\"3/8\",\"1/2\",\"5/8\",\"3/4\",\"7/8\")"}]
[null,null,null,{"$f":"sbGetCell(C13,6)","v":""},{"$f":"sbGetCell(C13,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C14,6)","v":""},{"$f":"sbGetCell(C14,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C15,6)","v":""},{"$f":"sbGetCell(C15,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C16,6)","v":""},{"$f":"sbGetCell(C16,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C17,6)","v":""},{"$f":"sbGetCell(C17,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C18,6)","v":""},{"$f":"sbGetCell(C18,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C19,6)","v":""},{"$f":"sbGetCell(C19,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C20,6)","v":""},{"$f":"sbGetCell(C20,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C21,6)","v":""},{"$f":"sbGetCell(C21,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C22,6)","v":""},{"$f":"sbGetCell(C22,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C23,6)","v":""},{"$f":"sbGetCell(C23,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C24,6)","v":""},{"$f":"sbGetCell(C24,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C25,6)","v":""},{"$f":"sbGetCell(C25,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C26,6)","v":""},{"$f":"sbGetCell(C26,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C27,6)","v":""},{"$f":"sbGetCell(C27,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C28,6)","v":""},{"$f":"sbGetCell(C28,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C29,6)","v":""},{"$f":"sbGetCell(C29,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C30,6)","v":""},{"$f":"sbGetCell(C30,41)","v":""}]
[null,null,null,{"$f":"sbGetCell(C31,6)","v":""},{"$f":"sbGetCell(C31,41)","v":""}]