Rich wants to know how to allocate a contract amount of N months, where N is a value shown in column A. Rich is looking for a formula, no VBA. Today's Duel shows a few solutions.
Transcript of the video:
Hey, welcome back.
It's time for another dueling Excel Podcast.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 152. Allocate without VBA.
Today's question sent in from YouTube, Rich E.
He has a contract amount here in column B and the number of months in the contract.
He's very specific. He says he wants just formulas.
No VBA that will allocate that amount over the right number of months.
So, 19,000 divided by 6, fills in six months and then is blank for the rest of the time.
So, as I start to think about this, I'm going to select this whole range over here and I want to figure out which month number I'm in.
So, equal COLUMN, open paren, close paren.
Gives me the Column number.
But it's not really the month number.
Its incremented by two.
So, January is in column C.
Column C is the third column.
I need to check and see if this is less than or equal to the number of months.
If it is, I'm going to have a number there.
So, less than or equal to in parentheses.
A2, but not just A2.
I want the dollar sign before the A.
So I'm going to press F4 three times.
Three times to lock the column down, plus two.
Ctrl+Enter and sure enough anytime that I have it TRUE there, that's where I want a number to be.
Anytime that I have a FALSE, that's where I want nothing to be.
You know, I could put an IF statement in here but I'm going to use the Boolean trick.
We're just going to wrap that whole thing in parentheses and multiply it times our calculation.
The calculation is the contract amount in B.
F4 three times, divided by the number of months in A.
Again F4 three times.
And that should give us what we want.
However, it's given me zeros out here when in fact, we want to show blanks.
So, Ctrl+; so, Ctrl+1 I am going to go to the Custom number format and I'm going to leave that as the positive number format, negative and zero.
So, semicolon, semicolon is going to show up as blank and there we have something that returns that looks like the correct result.
These cells on here aren't actually blank but they certainly appear blank.
They're going to print blank and hopefully that will do it.
Mike, let's see what you have.
Thanks MrExcel.
Oh, my heavens! That's two weeks in a row.
You've done Boolean magic.
That is beautiful and I'm going to slink on over to this sheet and just do a straight IF.
I'm going to say IF and now I need a number incrementer because one, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve.
I always need to compare that to whatever the number of months are.
So, I'm going to use the COLUMNS with an S.
Now this function counts how many columns there are in a range and I need one two three.
Hey, I'm sitting in C2 so, I'm going to type dollar sign. C2:C2.
Notice, the C is locked on the first one but not the second one.
So, when I copy it to the side that range will expand and give me one, two, three, four, five.
Whenever that is greater than that number of months in the contract, F4, one, two, three times. So, lock the COLUMN.
Needs to be locked this way but move relatively when we copy down to the next row where we want six.
Hey, that's our logical test.
IF that's true, meaning COLUMNS are greater, in this case, it would be already in column 2 then I want... Hey, I'm going to put double-quote, double-quote, a null text string.
That's the syntax to show nothing.
Otherwise, I want... Hey, I'm out.
One, two, three times with F4 divided by Oop! One, two, three close parentheses, Ctrl+Enter.
Now, I'm going to copy this.
F5 to bring up the Go To.
I notice the destination cell, way over here in the corner is N7.
Before I click Ok or Enter, I am going to hold Shift.
So, when this causes the cell reference to jump over here, when I hold Shift, it will highlight.
So, Shift+Enter and then Ctrl+V.
Now, I'm going to Control Period.
Period to get to the end and hit F2.
Just to check and see if that works.
Yeah! That looks like it works.
All right, throwback to MrExcel.
Whoa Mike!
You know, you think it's about a formula, it's not.
This is so cool.
Hold down the Shift key. You click Ok.
And it selects out to that range.
That is really, really cool.
Hey! I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel Podcast from MrExcel and Excel Is Fun.
It's time for another dueling Excel Podcast.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 152. Allocate without VBA.
Today's question sent in from YouTube, Rich E.
He has a contract amount here in column B and the number of months in the contract.
He's very specific. He says he wants just formulas.
No VBA that will allocate that amount over the right number of months.
So, 19,000 divided by 6, fills in six months and then is blank for the rest of the time.
So, as I start to think about this, I'm going to select this whole range over here and I want to figure out which month number I'm in.
So, equal COLUMN, open paren, close paren.
Gives me the Column number.
But it's not really the month number.
Its incremented by two.
So, January is in column C.
Column C is the third column.
I need to check and see if this is less than or equal to the number of months.
If it is, I'm going to have a number there.
So, less than or equal to in parentheses.
A2, but not just A2.
I want the dollar sign before the A.
So I'm going to press F4 three times.
Three times to lock the column down, plus two.
Ctrl+Enter and sure enough anytime that I have it TRUE there, that's where I want a number to be.
Anytime that I have a FALSE, that's where I want nothing to be.
You know, I could put an IF statement in here but I'm going to use the Boolean trick.
We're just going to wrap that whole thing in parentheses and multiply it times our calculation.
The calculation is the contract amount in B.
F4 three times, divided by the number of months in A.
Again F4 three times.
And that should give us what we want.
However, it's given me zeros out here when in fact, we want to show blanks.
So, Ctrl+; so, Ctrl+1 I am going to go to the Custom number format and I'm going to leave that as the positive number format, negative and zero.
So, semicolon, semicolon is going to show up as blank and there we have something that returns that looks like the correct result.
These cells on here aren't actually blank but they certainly appear blank.
They're going to print blank and hopefully that will do it.
Mike, let's see what you have.
Thanks MrExcel.
Oh, my heavens! That's two weeks in a row.
You've done Boolean magic.
That is beautiful and I'm going to slink on over to this sheet and just do a straight IF.
I'm going to say IF and now I need a number incrementer because one, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve.
I always need to compare that to whatever the number of months are.
So, I'm going to use the COLUMNS with an S.
Now this function counts how many columns there are in a range and I need one two three.
Hey, I'm sitting in C2 so, I'm going to type dollar sign. C2:C2.
Notice, the C is locked on the first one but not the second one.
So, when I copy it to the side that range will expand and give me one, two, three, four, five.
Whenever that is greater than that number of months in the contract, F4, one, two, three times. So, lock the COLUMN.
Needs to be locked this way but move relatively when we copy down to the next row where we want six.
Hey, that's our logical test.
IF that's true, meaning COLUMNS are greater, in this case, it would be already in column 2 then I want... Hey, I'm going to put double-quote, double-quote, a null text string.
That's the syntax to show nothing.
Otherwise, I want... Hey, I'm out.
One, two, three times with F4 divided by Oop! One, two, three close parentheses, Ctrl+Enter.
Now, I'm going to copy this.
F5 to bring up the Go To.
I notice the destination cell, way over here in the corner is N7.
Before I click Ok or Enter, I am going to hold Shift.
So, when this causes the cell reference to jump over here, when I hold Shift, it will highlight.
So, Shift+Enter and then Ctrl+V.
Now, I'm going to Control Period.
Period to get to the end and hit F2.
Just to check and see if that works.
Yeah! That looks like it works.
All right, throwback to MrExcel.
Whoa Mike!
You know, you think it's about a formula, it's not.
This is so cool.
Hold down the Shift key. You click Ok.
And it selects out to that range.
That is really, really cool.
Hey! I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel Podcast from MrExcel and Excel Is Fun.
