How To Calculate Check Digit (MOD 10)
How do I create a formula that will calculate a check digit for a 9digit number using MOD 10? The weighting is 212121212. The problem I am having is that if the sum of the product is in double digits, they must be treated as individual digits.
Example:
Number To Be Calculated: 0 0 4 4 5 5 5 2 5
Weighting: 2 1 2 1 2 1 2 1 2
Sum: 0+0+8+4+1+0+5+1+0+2+1+0
22/10 = 2 r2
102 = 8 (Check Digit)
Is it possible to create such a formula? I need to calculate several hundred and do not want to do this manually!
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macro To Force Format And Check Barcode Check Digit
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts 8 digit should be "#### ####" 12 digit should be "###### ######" 13 digit should be "# ###### ######" 14 digit should be "# ## ##### ######" these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
View Replies!
View Related
Sequential Check Digit Validation
I have a spreadsheet that pulls in a log file after running a macro with 100000eight digit numbers which may or may not have a record in the system. These numbers are produced by using any seven digits and adding a check digit at the end for an eight digit number using the mod function specifically Mod7...=MOD(A1,7) , for example, if cell A1 has 1234567 the mod7 function will produce the check digit 5 then concatenating for the eight digit number 12345675. the next number will be produced by adding 1 to the end of the 7 digit number 1234567+1=1234568 then concatenating again the number with the check digit 6, 12345686. this would be sequential order. my spreadsheet gets all the eight digit numbers with no record and sorts them in MOD7 sequential order in one column. since the spreadsheet ignores the eight digit numbers with records. my compiled list of numbers have non sequential numbers in the list. i need a way in VB to loop through 100000 lines (column A) and find the first and last number of a MOD7 sequential range and place the first number in (column B) and the last in (column C). I have figured a way to do this in a spreadsheet but it takes too many functions and calculations in 100000 lines so the spreadsheet takes too long to open and sometimes crashes!
View Replies!
View Related
Check Digit For Container Numbers
I wanna create a buildin check digit for container numbers so it will exists out of 11 cells. ( GATU 0210979 ) is it possible when u type any letter/number in the first cell on a row it automaticly jumps to the next cell, repeating this for the full container number ?
View Replies!
View Related
Check 4 Digit Fiscal Year Is Entered In A Cell
This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place. Here is my
View Replies!
View Related
Seperate 2 Digit Number Into 2 Single Digit Numbers
I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?
View Replies!
View Related
MOD 10 Calculting
I have that formula on excell to calculate the MOD 10 value for a DIGIT CHECK. BELOW THE FORMULA. =10MOD(SUMPRODUCT(MID(A1,{1,3,5,7,9,11,13,15,17},1))+ SUMPRODUCT(MID(A1,{2,4,6,8,10,12,14,16,18},1)*3),10) It works for a number but it is not working for the other. example datas : 5290000040000850120 /// THE RIGHT NUMBER IS : 2 it is ok for that number: the other number : 5290000040000850044 // the right number : 4 after the 44
View Replies!
View Related
MOD Formula
Trying to find a formula in Colm. X that will take the negatives in column W and order in increments of 5. Example  if the ending balance is negative 6, then order 10, if end balance is neg 12, then order 15, if end bal is 22, order 25. Do I need a MOD formula? ....
View Replies!
View Related
MOD Func
The below is simple: If I type 3 in A1 the formula returns 1. If I type 1 in A1 the formula returns 2. If I type 2 in A1 the formula returns 3. I want the opposite. If I type 2 in A1 it returns 1. If I type 1 in A1 it returns 3. If I type 3 in A1 it retunrs 2. ******** ******************** ************************************************************************>Microsoft Excel  PPT.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3= ABCD13***2****31***Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Mod And Arrays
I am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results. Sub test() Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer MyArray = Array("test 1", "test 2", "test 3", "test 4", "test 5") RowCount = Cells(Rows.Count, 1).End(xlUp).Row ArrayCount = UBound(MyArray) For i = 1 To RowCount Range("B" & i).Value = MyArray(i  1 Mod ArrayCount) Next i End Sub
View Replies!
View Related
MOD Function
what the hek the MOD function is supposed to do. According to the MS Help file, it returns the remainder of a number divided by another number. Now, when I was in school, the remainder was pretty much anything after the decimal. That doesn't seem to be the case in Excel, however. For the examples here, let A1 equal 5. The formula =MOD(A1,2), as I see it, should return 5 since 5/2 equals .50, but it doesn't; it returns 1. Further, 5/3 equals .67, but =MOD(A1,3) returns 2 (what, is it rounding?) There's a lot of talk about MOD being used to return even/odd results, but does this work when the MOD function uses 1 or 3 (or higher) as the divisor?
View Replies!
View Related
Year Planner Mod
I have a copy of a year planner that calculates the days of the month and adjusts them according to the year input into the header area. Would anyone please modify it so that the first column reads August and the last column reads July (instead of Jan to Dec) and still maintain the calculations as required?
View Replies!
View Related
MOD Operator Ambiguity
MOD Operator gives one result when this is used as worksheet function and the different one when I use that in VBA. Cell A1 = 01Jan08 Cell A2 = 02Jan08 In Worksheet =MOD(A1A2,8) Gives 7 In VBA Reminder = Range("A1")Range("A2") Mod 8 Gives 1
View Replies!
View Related
MOD Function In VBA
In the excel worksheet, I use MOD(YEAR();4) to determine whether there are 365 or 366 days in that year. I would like to use this function also in my User Defined Function VBA Code. I cannot find how to do this.
View Replies!
View Related
24 Digits Mod Function
how can i do this in excel 2002? for example..... mod(95000000922019182020281000,97)=24 but in excel im getting the value as 0 also when i type the 24 digits its show like ..... # NUM! if i put the function like =A1FLOOR(A1,B1) its working for 13 digits but not for 24 digits.
View Replies!
View Related
MOD+ROW Conditional Formating
I have an excel document that has one columb (I) of cells that changes color depending on a specific date (columb h) . The cells that have conditional formating to change the row color based on where it is ( =MOD(ROW(),2)=1), stays blank and the proper color until columb A is filled in. The others show up red. How would I code/format the lines that aren't included in the conditional formating to always show white when no value is entered in columb a.
View Replies!
View Related
Insert Code Into This Workbook Mod
I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened. How would I go about doing that? I have this code so far to open the workbooks that are in the folder.
View Replies!
View Related
Passing Variables To Other Subroutines In Same Mod
I'm having issue with passing along a variable. I learned this morning how to pass these along. But for this instance something isn't working correctly. The code is below: I cut out all the subs in between so you can see the problem.I think it has something to do with "File search" The problem is after sub New_Pro_Test runs then returns back to Sub RunFolder I get subscript out of range error. It is suppose to open the next workbook in that folder.
View Replies!
View Related
Change MOD Divisor Onthefly
I have data in column C starting cell 2 that runs sometimes to 50, sometimes 80 or more rows. I would like column D to MOD the numbers in C, but in cell E2, I would like to enter the MOD divisor and have Excel use whatever number I put into E2 to MOD column D. What formulas would I use for this? I've attached an eg in case I am not clear.
View Replies!
View Related
Mod Function Works Wrong
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(6,7) = 1 in Excel. So I got to explaining that 6 Mod 7 is the same as 6(6/7)*7 which is how you get 1. And that's when I realized... 6/7 = 0 not 1. Then I looked in VBA and sure enough 6 mod 7 = 6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT. So here is how it work out in excel: 6(6/7)*7 6(0.857142857)*7 6(1)*7 67 6+7=1 But in VBA you get 6(6/7)*7 6(0.857142857)*7 6(0)*7 60=6
View Replies!
View Related
Implement The Mod Function In VBA?
VBA 2003 does not support MOD (modulo arithmetic) as a WorksheetFunction, and there does not seem to be a direct MOD function in VBA. I've been dabbling with code that would do the same thing as MOD, so far with no success. Does anyone know how to do this in VBA? I assume it would be some combination of If...Then, Round (up or down), maybe a compare, and perhaps an iteration loop. I would think such a routine would be useful to many people.
View Replies!
View Related
MOD Function: Color Every Second Row
I work for a training company based near Gatwick. We are currently producing a new course and one of the new functions we are using is the MOD function. We are using it to colour alternate lines (Conditional formatting). The formula works great, but we do have a few mathematical questions on it. The formula is: =MOD(Row()C,N*2)+1<=N The question I have is about the sections in red, why are they there and why do they make it work?
View Replies!
View Related
Converting 2 Digit Year Into 4 Digit Year
I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it. Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with). Example: 98 > 1998 99 > 1999 00 > 2000 01 > 2001
View Replies!
View Related
INDIRECT(MOD(ROW)) Skipping A Lot Of Rows
I'm currently working on a report and what I'm trying to do is get a Row of information to pull into 4 rows. My current formula looks like this: =INDIRECT("'Paste SAP'!H"&IF(MOD(ROW()1,4)=1,ROUNDDOWN(((ROW())+3/4),0)," "),1) I change the bolded number to correspond to which row (1,2,3,0) but it's not functioning. I've done it with other but for some reason this one doesn't work. I've attached the template so you can see what it looks like. The problem is with the SAP Tab and the info from the Paste SAP tab.
View Replies!
View Related
Recursive Division Macro Mod Needed For Char Type
I need a slight mod to the below code which works perfectly otherwise. What it does is it divides whatever is in column K by 30 and puts in its relative cell in column M( There a bunch of mini tables below each other with blank rows in between). The thing is sometimes there are characters in column K like "N/A" for instance and the macro crashes since its only designed to take into account numerical and blank cells in column K.
View Replies!
View Related
15 Digit Sequence
My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is: =IF(MID(C378,LEN(C378)1,1)="W",C378&" ,",C378) And what that does is it would change data in a cell that looks like this 010203405W2 TO 020304506W2 To this: 010203405W2 TO 020304506W2 , The formula works great, thanks to the help of the posters! However I just ran into an issue, what if I have a set of data in a cell that looks like this: 010203405W2 TO 020304507W2 020203405W2 TO 020304507W2 030203405W2 TO 020304507W2 And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied: 010203405W2 TO 020304507W2 , 020203405W2 TO 020304507W2 , 030203405W2 TO 020304507W2 , Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.
View Replies!
View Related
Is A Number Or A Digit
how you can determine if a cel value is a number or a digit It is actually for a column to determine if it has a heading or not and acordingly y have to count the rows off that "current region" and put some values with a formula next to this column. Y was testing an expression with this msg = IIf(target.End(xlUp).Value = "X", "is this a LETTER", "is this a DIGIT")
View Replies!
View Related
Sorting By First Digit Only
I am trying to sort a spreadsheet in descending order of a certain column. I have been able to do so quite easily in the past. However, for some reason, when I am trying to sort it now, it is sorting based only on the first digit of the number in the cell. For example, the order after sorting may end up as 90, 9, 9, 82, 8, 8, 8, 74, etc. I don't know what happened to cause the change, or how to fix it.
View Replies!
View Related
Counting Occurrences Of Particular Digit
I have a slight problem...I have a range of values.. 0113 1240 8430 0903 I need to count the occurance of a a particular no. from 09.. So i want to know how many times say 0 appears in that range of values etc so on till 9. I tried using CountIF but the problem i face is lets say in my cell 0903 there are 2 0s inside, it doesnt count this 2 zero..
View Replies!
View Related
Change Last Digit Of A Number To 5 Or 9
The last digit of a number (can be any demonination but always an integer). I know that you can check the last digit of a number with =RIGHT(A1,1) but I would like to check whether the last digit in number is 1,2,3,4,5 and if so change the whole number to a number ending in 5. If the original number ends in 0,9,8,7,6 then I want it to be changed to end in 9. Is this possible and if so is there an excel function that I could use?? e.g. 143 > 145 e.g. 1037 > 1039
View Replies!
View Related
2 Digit Number Combination
I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9 it would look like this 4 0 3 9 0 4 3 3 2 9 2 5 6 5 6 9 6 6 8 9 9 3 1 0 2 9 8 1 6 7 5 9 9 8 2 5 2 7 2 2 2 8 5 1 3 0 1 4 7 4 7 6 0 9 3 5 6 4 3 3 0 4 4 7 8 8 1 8 5 7 8 7 5 4 1 8 6 1 1 5 1 6 3 0 0 7 2 4 7 0 each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.
View Replies!
View Related
Get One Cell To Increase By Digit One
How can I get one cell to increase by 1 after I change the date in another cell. The spreadsheet is used to keep track of Oxygen Bottles at the local Fire Dept. and I want the " Times Filled" cell to increase by 1 after the " Date Filled" cell is changed. I'm not sure if they key the date in or if they use a calender addin pop up.
View Replies!
View Related
6 Digit Code To Date.
I have many dates in a column in a 6 digit format such as: 950223 950224 950225 These dates are Feb 23, 1995; Feb 24, 1995 etc. How can I convert these dates in the column to 2/23/1995, 2/24/1995 etc?
View Replies!
View Related
Converting Digit To Hour
I have simple (or not) question: how can I convert a digit that I input into a cell to an hour format ? I want to achieve something like this:  when I input a digit into a cell , for example: 9 a want to convert it to 9:00 (9 hours, 0 minutes). How can I do it ?
View Replies!
View Related
Two Digit Year Changes On Import
I am importing data from a .csv file. The file contains dates like 02/15/40. The year is 2040, not 1940. Excel doesn't seem to use the Control Panel > Regional Settings change when I open this file. I have my Regional Settings set to 2000 => 2099 to change to a two digit year. If I open a blank worksheet and type in 2/15/40 in a cell...Excel does the correct conversion to 2040. However, when I open a .csv file it considers it 1940.
View Replies!
View Related
12 Digit Number Into Date/time
20081110120000[0:GMT] is there a way to convert many entries of dates and time entered in the above way into actual dates and times? is it as simple as making a custom format? if so, whats the syntax? the entries appear to be the year(4 characters), the month (2 characters), the day of month (2 characters), and the time of day (6 characters, AM/PM) i dont need the time, so the last part ("120000[0:GMT]") isn't necessary.
View Replies!
View Related
Statistical Analysis Of Three Digit Numbers
using Excel 2007, windowsXP, and am trying to find the right formula for the following problem. I have a column of 3 digit numbers, and want to determine how many times a number occurs in the first position ie: 123 the second position ie: 123 and the third position ie: 123 in the entire column of figures?
View Replies!
View Related
How Do I Get The Dashes To Not Count As A Digit/number?
I have this script that does what i need it to do but one thing, I need the script to count the numbers as digits only and not the dashes in the format. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.EnableEvents = False If (Len(Target) > 11) Or (Len(Target) < 10) Then MsgBox "Not a valid number of characters, please try again" Target.Value = vbNullString...................
View Replies!
View Related
Generate A Random 9 Digit Number
I have a spreadsheet with approx 11,000 rows and I would like to generate a unique 9 digit number for each line. I know I could just put 100 000 000 and then increase that by one all the way down but I was wondering if there was any formula or code
View Replies!
View Related
Adding A Digit Into Column Of 2 Sheets
i am working in office. my boss has given me 68 work sheets to analyze. i have completed all the task except one. i want to add a capital D to the left of numbers into column B of 68 worksheets. excel sheet is attached. it is only sample. i have more than 6000 record in each work sheet. but how can i do this in only one click.
View Replies!
View Related
