British Cattle (Really) - 1029 - Learn Excel from MrExcel Video

British Cattle (Really) - 1029 - Learn Excel from MrExcel

Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Basic, you start out with massive amounts of data, you say how are we gonna analyze this, well, let’s fire up an activity table and see if we can solve this problem. Welcome back to the Mr. Excel netcast, I’m Bill Jelen. Sarah, from England says that if I could solve this problem, it will help out tens of thousands of British cattle farmers. It applies to no one else other than British cattle farmers, these were the ID numbers issued to British cattles. The first bit here, this 8 is static, that’s assigned to the farm, but then this number is incredibly annoying. The 804, 805, 806, that continues increasing indefinitely, but the first digit scales from one to seven and then starts over again at one. And so Sarah says, this is driving me crazy, because, it’s six increases of 100,001, followed by one decrease of 499,999, and how are you gonna write a formula for that. So, I wrote two formulas, now the first one here, let’s do this, we’re gonna do this static part as a static part. So, UK,1-2-3-4-5-6, Sarah says that they end up entering this all 100 manually, coz just they can't figure out a good way to solve it. So there’s the static part, ampersand, so here’s where some of the magic is going to happen, I’m gonna take the mod of the row of A1, comma, 7. Now, what does that do, that divides the row number by 7 and gives me the remainder. That’s gonna be a number from 0 to 6, I need to add one to that, plus one. Ampersand, now I’m gonna use the text function, the text function, the next one that I want is 817, so I want the row of A817, comma, five zeros, 1-2-3-4-5, that would put the leading zeros in there. So, I’m yet, UK, alright, type the initial text row, good and then instead of a 2, we’re leaving a 6, so I need to adjust that row number, to me to get a 7, here I meant to do a 5, close A6. Alright, so there we go, we have this, let’s copy it down, now that’s the real test, will it work when I copy it down. So the seven goes to 1-2-3-4, looks good and in fact it is incrementing the numbers. So there is one formula that will solve that problem. Big incantination formula, but, you enter it once, you know, you’re gonna put this on a sticky note on the wall, copy it just, kind of do a little test here with the row, to figure out which row to use as your starter row, coz that way it gives the first number. Once you get that, you’re golden, you can roll and everything will work. Now, another way to go here is actually I put the static part in column A and then built a formula here that looks at the mod of the row divided by 7, sees of it, it’s equal to zero, if it is, it subtracts the 5999, actually that should be 4999 and otherwise it adds a 1001 to the previous value. And so this is working out just fine and then of course, we need a incantination formula here, ampersand quote, space, quote, ampersand, and copy that down all the way. Now of course, once you get this, you’re gonna want to convert those to values so remember the good way to do this, there’s many ways you convert formulas to values, but go to the right edge, right click, drag right, drag left, let go of the right mouse button and copy here is values only and you have your answer. So, there you go, Sarah, it’s up to you to let the 10,000 other cattle farmers in the UK know about Mr. Excel.com and this amazing formula, I would use this one back here. To be honest, in fact, you give them, create a little macro, maybe they would do it, whereas for the starting number and right the formula, that would have to be podcast for another day. Hey, I wanna thank you for stopping by, see you next time for another netcast from Mr. Excel.

No Links were listed yet. Go ahead and share!

Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Basic, you start out with massive amounts of data, you say how are we gonna analyze this, well, let’s fire up an activity table and see if we can solve this problem. Welcome back to the Mr. Excel netcast, I’m Bill Jelen. Sarah, from England says that if I could solve this problem, it will help out tens of thousands of British cattle farmers. It applies to no one else other than British cattle farmers, these were the ID numbers issued to British cattles. The first bit here, this 8 is static, that’s assigned to the farm, but then this number is incredibly annoying. The 804, 805, 806, that continues increasing indefinitely, but the first digit scales from one to seven and then starts over again at one. And so Sarah says, this is driving me crazy, because, it’s six increases of 100,001, followed by one decrease of 499,999, and how are you gonna write a formula for that. So, I wrote two formulas, now the first one here, let’s do this, we’re gonna do this static part as a static part. So, UK,1-2-3-4-5-6, Sarah says that they end up entering this all 100 manually, coz just they can't figure out a good way to solve it. So there’s the static part, ampersand, so here’s where some of the magic is going to happen, I’m gonna take the mod of the row of A1, comma, 7. Now, what does that do, that divides the row number by 7 and gives me the remainder. That’s gonna be a number from 0 to 6, I need to add one to that, plus one. Ampersand, now I’m gonna use the text function, the text function, the next one that I want is 817, so I want the row of A817, comma, five zeros, 1-2-3-4-5, that would put the leading zeros in there. So, I’m yet, UK, alright, type the initial text row, good and then instead of a 2, we’re leaving a 6, so I need to adjust that row number, to me to get a 7, here I meant to do a 5, close A6. Alright, so there we go, we have this, let’s copy it down, now that’s the real test, will it work when I copy it down. So the seven goes to 1-2-3-4, looks good and in fact it is incrementing the numbers. So there is one formula that will solve that problem. Big incantination formula, but, you enter it once, you know, you’re gonna put this on a sticky note on the wall, copy it just, kind of do a little test here with the row, to figure out which row to use as your starter row, coz that way it gives the first number. Once you get that, you’re golden, you can roll and everything will work. Now, another way to go here is actually I put the static part in column A and then built a formula here that looks at the mod of the row divided by 7, sees of it, it’s equal to zero, if it is, it subtracts the 5999, actually that should be 4999 and otherwise it adds a 1001 to the previous value. And so this is working out just fine and then of course, we need a incantination formula here, ampersand quote, space, quote, ampersand, and copy that down all the way. Now of course, once you get this, you’re gonna want to convert those to values so remember the good way to do this, there’s many ways you convert formulas to values, but go to the right edge, right click, drag right, drag left, let go of the right mouse button and copy here is values only and you have your answer. So, there you go, Sarah, it’s up to you to let the 10,000 other cattle farmers in the UK know about Mr. Excel.com and this amazing formula, I would use this one back here. To be honest, in fact, you give them, create a little macro, maybe they would do it, whereas for the starting number and right the formula, that would have to be podcast for another day. Hey, I wanna thank you for stopping by, see you next time for another netcast from Mr. Excel.

Transcription by: Scribe4you Transcription Services