Get instant live Excel expert help to learn how to round a number in Excel

Basically for each entry in the "Packet Games" section we can have multiple winners and they can be both Single and Double winners. One single winner would win 40.00 as long as they are the only winner, and the payout needs to round up to the nearest .25 If n15>0 then calculate using O13 to complete Roundup(O13/C5/0.25,0)*0.25. The excel roundup function is not working

How to round up in excel, my total needs to round up to the nearest .25, needs to look at another cell and if there is an entry split the total.

I'm trying to build a spreadsheet to keep tracking of scoring/player for each round of an outing. I need help syncing players names to tables so it auto updates based on their name, I also need help with an if analysis to show 0 if the cells are blank. I hope that makes sense, it's kind of complicated. Thanks!

I have a spreadsheet created. However, I need the formula to work off of text too, not just a number. This is my formula: =IF(AND(K5>0,L5>0,M5>0),ROUND(VLOOKUP(TEXT(K5,"00.000")&TEXT(L5,"0.000"),'Shape, Support Tooling'!A2:G19,5+M5,FALSE),0),"") Where M5 is, is where I would need the column to have words in it instead of going off numbers

I need to round =(I3-H3)/H3 down to 2 decimal places but I'm can't figure out how to round decimals.

Hi! I created a table I think I did ok so far but I'm stuck when the punch in is on one date and the punch out date is another date. So please hopefully you can help me, it would greatly be appreciated! I don't need to add lunch breaks but I do have to round up on punch in and round down for punch outs to nearest 15 minutes. Punch in time is rounded up to the nearest 15 minutes 8:01 - 8:14 = 8:15 8:16 - 8:29 = 8:30 8:31 - 8:44 = 8:45 8:46 - 8:59 = 9:00 I used this formula : =CEILING(F23,"0:15") Punch out time is rounded down nearest 15 minutes 8:01 - 8:14 = 8:00 8:16 - 8:29 = 8:15 8:31 - 8:44 = 8:30 8:46 - 8:59 = 8:45 I used this formula: =FLOOR(H23+"0:01","0:15") Then I have the cell that formulates the hours clocked I used this formula: =TEXT(I23-G23,"H:MM") The final cell then formulates the total hours I used this formula: =(J23-INT(J23))*24 As I mentioned above, the issue I'm having is punch in on one date and punch out on the following date. I tried pasting example here but it didn't show. Thank you in advance :)

hi, i need to make formula so if i say in cell a2 =yes i want be to b2 to say £150.00 i have tried if function but need to do it wrong way round and can not get my head around it

Private Sub FedexPart3(SKUCOL, FUNSKUCOL, QTYCOL, SELLINGCOL, TOTALCOL, WEIGHTCOL, COMMODCoL, DESCCOL, ADDRESSCOL, BoxLetterCol, ASINCOL, EANCOL, FBASHIPCOL, BOXQTYCOL, BoxWeightCol, BoxDimsCol, FBAIDCOL, ADDCVDCOL, SHIPLETTERCOL, CVDCOL) shipid = InputBox(Prompt:="FBA Shipment ID", Title:="Please Enter the FBA Shipment ID you wish to process") planid = InputBox(Prompt:="Shipment Plan ID?", Title:="Enter Shipment Plan ID") shipdate = InputBox(Prompt:="Please enter an Shipment Date in the following format: 'dd/mm/yyyy'", Title:="Enter Shipment Date") Call clearinvoices Call GetHeader(shipid, shipdate) Application.ScreenUpdating = False 'Create Folder Directories basedir = ActiveWorkbook.Path & "\Shipments\" If Len(Dir(basedir, vbDirectory)) = 0 Then MkDir (ActiveWorkbook.Path & "\Shipments\") End If If Len(Dir(basedir & shipid & "\", vbDirectory)) = 0 Then MkDir (basedir & shipid & "\") End If If Len(Dir(basedir & shipid & "\" & "Commercial Invoice" & "\", vbDirectory)) = 0 Then MkDir (basedir & shipid & "\" & "Commercial Invoice" & "\") End If commercialdir = basedir & shipid & "\" & "Commercial Invoice" & "\" If Len(Dir(basedir & shipid & "\" & "Packing Slips" & "\", vbDirectory)) = 0 Then MkDir (basedir & shipid & "\" & "Packing Slips" & "\") End If packingdir = basedir & shipid & "\" & "Packing Slips" & "\" 'Create Packing Slips Sheets("MASTERSHEET").Select m = 2 Range("T2").Select Selection.Sort key1:=Range("T2:T65536"), order1:=xlAscending, Header:=xlYes Range("R2").Select Selection.Sort key1:=Range("R2:R65536"), order1:=xlAscending, Header:=xlYes mancnt = Sheets("MASTERSHEET").Range("R65536").End(xlUp).Row startrow = 12 lastboxchar = Range(BoxLetterCol & m).Value boxno = 1 While m <= mancnt + 1 Sheets("MASTERSHEET").Select fbaid = Range(FBAIDCOL & m) boxchar = Range(BoxLetterCol & m).Value sku = Range(SKUCOL & m) commod = Range(COMMODCoL & m) desc = Range(DESCCOL & m) totalqnt = Round(Range(BOXQTYCOL & m), 0) If shipid = Trim(fbaid) Then If lastboxchar = boxchar Then Sheets("PACKING SLIPS").Select If Not Sheets("PACKING SLIPS").Range("D" & startrow) = "" Then Range("A" & startrow).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove End If Range("A" & startrow & ":C" & startrow).Merge Range("A" & startrow) = sku & " " & desc Range("D" & startrow) = commod Range("E" & startrow) = Round(totalqnt, 0) Range("A" & startrow).WrapText = True Range("A" & startrow).RowHeight = 40 lastboxchar = boxchar m = m + 1 Else If Not lastboxchar = "" Then Sheets("PACKING SLIPS").Select Sheets("PACKING SLIPS").Range("A7") = "Box" & " " & boxno Sheets("PACKING SLIPS").Range("B7") = "Box" & " " & lastboxchar Sheets("PACKING SLIPS").Range("E" & startrow) = Application.WorksheetFunction.Sum(Range("E12:E" & startrow - 1)) 'print packing slips ActiveSheet.PageSetup.PaperSize = xlPaperA4 ActiveSheet.PageSetup.PrintArea = "A1:E" & startrow ActiveSheet.PageSetup.Orientation = xlLandscape ' ****************************************** ' Reactivate Prints after development ' ActiveSheet.PrintOut ' ****************************************** Call savepackingslips(boxno, shipid, packingdir) startrow = 11 boxno = boxno + 1 lastboxchar = Sheets("MASTERSHEET").Range(BoxLetterCol & m).Value Call resetpackingslips Else lastboxchar = Sheets("MASTERSHEET").Range(BoxLetterCol & m).Value End If End If startrow = startrow + 1 Else m = m + 1 End If Wend 'Save final packing slip in shipment Sheets("PACKING SLIPS").Select Sheets("PACKING SLIPS").Range("A7") = "Box" & " " & boxno Sheets("PACKING SLIPS").Range("B7") = "Box" & " " & lastboxchar Sheets("PACKING SLIPS").Range("E" & startrow) = Application.WorksheetFunction.Sum(Range("E12:E" & startrow - 1)) ActiveSheet.PageSetup.PaperSize = xlPaperA4 ActiveSheet.PageSetup.PrintArea = "A1:E" & startrow ActiveSheet.PageSetup.Orientation = xlLandscape 'ActiveSheet.PrintOut Call savepackingslips(boxno, shipid, packingdir) 'Create Commercial Invoice c = 2 itemstartcell = 22 Dim done As String 'Create Table Headers Sheets("COMMERCIAL INVOICE").Range("B21") = "QTY" Sheets("COMMERCIAL INVOICE").Range("C21") = "Description" Sheets("COMMERCIAL INVOICE").Range("D21") = "HS Code" Sheets("COMMERCIAL INVOICE").Range("E21") = "ADD/CVD Details" Sheets("COMMERCIAL INVOICE").Range("F21") = "EAN" Sheets("COMMERCIAL INVOICE").Range("G21") = "Weight (KG)" Sheets("COMMERCIAL INVOICE").Range("H21") = "Country Of Origin" Sheets("COMMERCIAL INVOICE").Range("I21") = "Unit Selling Price (USD)" Sheets("COMMERCIAL INVOICE").Range("J21") = "Total Selling Price (USD)" While c <= mancnt Sheets("MASTERSHEET").Select funsku = Range(FUNSKUCOL & c) fbaid = Range(FBAIDCOL & c) If shipid = Trim(fbaid) Then strpos = InStr(done, funsku) 'Debug.Print strpos If Not strpos >= 1 Then Address = Range(ADDRESSCOL & c) commod = Range(COMMODCoL & c) desc = Range(DESCCOL & c) kg = Range("J2:J" & c) sku = Range(SKUCOL & c) EANCode = Range(EANCOL & c) cvd = Range(CVDCOL & c) sellingprice = Range(SELLINGCOL & c) Set ws = ThisWorkbook.Sheets("MASTERSHEET") Set vistotal = ws.Range("W1:W" & mancnt) Set sortsku = ws.Range("A1:A" & mancnt) Set kg = ws.Range("P2:P" & mancnt) ws.AutoFilterMode = False Sheets("MASTERSHEET").Range("I1").AutoFilter Field:=9, Criteria1:=funsku totalweight = Application.WorksheetFunction.Sum(kg.SpecialCells(xlCellTypeVisible)) visibleTotal = Application.WorksheetFunction.Sum(vistotal.SpecialCells(xlCellTypeVisible)) Sheets("COMMERCIAL INVOICE").Select If Not Sheets("COMMERCIAL INVOICE").Range("I" & itemstartcell) = "" Then Range("B" & itemstartcell).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow End If Range("B" & itemstartcell) = Round(visibleTotal, 0) Range("B" & itemstartcell).HorizontalAlignment = xlCenter Range("C" & itemstartcell) = desc & " " & sku Range("C" & itemstartcell).WrapText = True Range("C" & itemstartcell).Rows.AutoFit Range("C" & itemstartcell).HorizontalAlignment = xlLeft Range("D" & itemstartcell) = commod Range("D" & itemstartcell).HorizontalAlignment = xlCenter Range("D" & itemstartcell).NumberFormat = "0" Range("E" & itemstartcell) = cvd Range("E" & itemstartcell).HorizontalAlignment = xlCenter Range("F" & itemstartcell) = EANCode Range("F" & itemstartcell).HorizontalAlignment = xlCenter Range("F" & itemstartcell).NumberFormat = "0" Range("G" & itemstartcell) = Format(totalweight, "#.000") Range("G" & itemstartcell).HorizontalAlignment = xlCenter Range("H" & itemstartcell) = Address Range("H" & itemstartcell).WrapText = True Range("H" & itemstartcell).Rows.AutoFit Range("H" & itemstartcell).HorizontalAlignment = xlLeft Range("H" & itemstartcell).VerticalAlignment = xlCenter Range("I" & itemstartcell) = sellingprice Range("I" & itemstartcell).HorizontalAlignment = xlLeft Range("J" & itemstartcell) = sellingprice * visibleTotal Range("K" & itemstartcell) = sku itemstartcell = itemstartcell + 1 End If c = c + 1 done = done & funsku & ", " 'Debug.Print done On Error Resume Next Sheets("MASTERSHEET").ShowAllData Else c = c + 1 End If Wend Sheets("COMMERCIAL INVOICE").Select lrow = Application.WorksheetFunction.Match("QTY", Range("b1:b200"), 0) 'Calculate total QTY, WEIGHT & SELLING PRICE Sheets("COMMERCIAL INVOICE").Range("D8") = "$" & Application.WorksheetFunction.Sum(Range("J" & lrow + 1 & ":J" & itemstartcell - 1)) Sheets("COMMERCIAL INVOICE").Range("B" & itemstartcell + 1) = Application.WorksheetFunction.Sum(Range("B" & lrow + 1 & ":B" & itemstartcell - 1)) Sheets("COMMERCIAL INVOICE").Range("J" & itemstartcell + 1) = Application.WorksheetFunction.Sum(Range("J" & lrow + 1 & ":J" & itemstartcell - 1)) Sheets("COMMERCIAL INVOICE").Range("G" & itemstartcell + 1) = Application.WorksheetFunction.Sum(Range("G" & lrow + 1 & ":G" & itemstartcell - 1)) 'update commercial invoice with box weights and dims Sheets("COMMERCIAL INVOICE").Select c = 2 While c <= mancnt boxid = Split(Sheets("MASTERSHEET").Range("T" & c), "B") Sheets("MASTERSHEET").Range("X" & c) = Int(boxid(1)) c = c + 1 Wend ' ActiveWorkbook.Worksheets("MASTERSHEET").Sort.SortFields.Clear ' ActiveSheet.Worksheets("MASTERSHEET").Range("$A$1:$W$117").AutoFilter Field:=19, Criteria1:=shipid ' Debug.Print "Filter Sheet" startcell = 11 oddstartcell = 11 evenstartcell = 11 boxcnt = 1 ActiveWorkbook.Worksheets("MASTERSHEET").Sort.SortFields.Clear ActiveWorkbook.Worksheets("MASTERSHEET").Sort.SortFields.Add Key:=Range("X1") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("MASTERSHEET").Sort .SetRange Range("A2:X881") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.AutoFilter ActiveSheet.Range("$S$1:$S$881").AutoFilter Field:=1, Criteria1:=shipid ' ActiveWorkbook.Worksheets("MASTERSHEET").AutoFilter.Sort.SortFields.Add Key:= ' Range ("X1:X117"), Header:=xlYes, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _ ' :=xlSortNormal ' With ActiveWorkbook.Worksheets("MASTERSHEET").AutoFilter.Sort ' .Header = xlYes ' .MatchCase = False ' .Orientation = xlTopToBottom ' .SortMethod = xlPinYin ' .Apply ' End With ' Debug.Print "Sort Sheet" c = 2 lastboxno = "1" While c <= mancnt fbaid = Sheets("MASTERSHEET").Range(FBAIDCOL & c) boxchar = Sheets("MASTERSHEET").Range(BoxLetterCol & c) boxweight = Sheets("MASTERSHEET").Range(BoxWeightCol & c) boxdim = Sheets("MASTERSHEET").Range(BoxDimsCol & c) If Len(boxchar) = 2 Then no = 1 Else no = 2 End If boxno = Right(boxchar, no) '****************************************************************** ' Needs to take into account multiple lines for each box If boxno = lastboxno Then If boxno Mod 2 = 0 Then Range("F" & oddstartcell) = boxchar Range("G" & oddstartcell) = Range("G" & oddstartcell) + boxweight Range("H" & oddstartcell) = boxdim lastbox = boxchar Else Range("B" & evenstartcell) = boxchar Range("C" & evenstartcell) = Range("C" & oddstartcell) + boxweight Range("D" & evenstartcell) = boxdim End If Else If boxno Mod 2 = 0 Then If boxno > 2 Then oddstartcell = oddstartcell + 1 End If Range("F" & oddstartcell) = boxchar Range("G" & oddstartcell) = boxweight Range("H" & oddstartcell) = boxdim lastbox = boxchar boxcnt = boxcnt + 1 Else evenstartcell = evenstartcell + 1 Range("B" & evenstartcell).EntireRow.Insert Shift:=xlDown Range("B" & evenstartcell) = boxchar Range("C" & evenstartcell) = boxweight Range("D" & evenstartcell) = boxdim lastbox = boxchar boxcnt = boxcnt + 1 End If End If c = c + 1 lastboxno = boxno Wend ActiveSheet.ShowAllData 'decval = Sheets("COMMERCIAL INVOICE").Range("D8").Value / 1.25 * 100 'Sheets("COMMERCIAL INVOICE").Range("F8") = "$" & decval Sheets("COMMERCIAL INVOICE").Range("D9") = boxcnt Sheets("COMMERCIAL INVOICE").Range("D9").HorizontalAlignment = xlLeft 'Print Commercial Invoice Sheets("COMMERCIAL INVOICE").Select ActiveSheet.PageSetup.PaperSize = xlPaperA4 ActiveSheet.PageSetup.PrintArea = "A1:I" & itemstartcell + 2 ActiveSheet.PageSetup.Orientation = xlLandscape ' ****************************************** ' Reactivate Prints after development ' ActiveSheet.PrintOut ' ****************************************** 'Call SavecommercialPDF(shipid, commercialdir) Call createpacklist(shipid, planid, SKUCOL, FUNSKUCOL, QTYCOL, SELLINGCOL, TOTALCOL, WEIGHTCOL, COMMODCoL, DESCCOL, ADDRESSCOL, BoxLetterCol, ASINCOL, EANCOL, FBASHIPCOL, BOXQTYCOL, BoxWeightCol, BoxDimsCol, FBAIDCOL, ADDCVDCOL, SHIPLETTERCOL, CVDCOL) End Sub The error or debug Sheets("MASTERSHEET").Range("X" & c) = Int(boxid(1)) boxid(1) = <subscript out of range>

I need help with excel round a number to the next whole number.04. This is for pricing purposes since prices are set in size ranges.

long decimal numbers to round up to just 2 places after the decimal. for the entire row