New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

How to repeat a cell depending on a value in another cell ?

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

How to repeat a cell depending on a value in another cell ?

Postby pbcaer » Tue Dec 13, 2011 8:14 am

ColA ColB ColC
300 1 2
600 3 4
112 5 10
140 11 65
Column A has these values, which I want to repeat in another range as many as the value between Col B and Col C. Ex., I want A1(300) to be repeated 2 times (2-1)+1..
Then I want to have a list somewhere else, something like this;

300
300
600
600
112
112
112
112
112
140
....

Any idea ?
  • 0

pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby Don » Tue Dec 13, 2011 6:47 pm

HI!

I understood what you were doing until you added 1 to the 2-1. Why did you do that?

Anyway, look into the REPT() function. This will repeat a value a given number of times. So, you could put this formula into cell D1:

Code: Select all
=REPT(A1,C1-B1)


If you want to add one to the count try this:

Code: Select all
=REPT(A1,(C1-B1)+1)
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Wed Dec 14, 2011 3:29 am

Hi,

There are no formulas in Excel that can write anything to any cell other than the one they occupy. Therefore your wish, if I understand it correctly, can't be fulfilled by worksheet formulas. You will need code for that.
Regards,
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: How to repeat a cell depending on a value in another cel

Postby pbcaer » Thu Dec 15, 2011 2:45 pm

well =rept does the job but it repeats the value in the same cell.. like this 300300300

what I need is to repeat a value in different cells..

here s the explanation..
Every 6 months (june n dec) the amount of the money ll increase.. so if the contract starts 4th month there will be 2 months to the first increase. and then the rest will go 6 by 6..

1 to 2 3 to 8 9 to 14 15 to 20 That's why I say the second value minus the first value plus 1 (hence we count the first and the last value both. ex. both 3 and 8 included)

Then I ll have a payment plan. Goes like 300 300 400 400 400 400 400 400 500...

When I have another customer who starts by Feb, then there plan would be 1 to 4 5 to 10 11 to 16 ...

What I need is a formula which ll repeat the pay amounts beside month values..
Repeat the first value as many as the difference between the last month and the first month plus one.
Thanks for help, in advance
  • 0

pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Fri Dec 16, 2011 2:42 am

Hi,

Two questions:
1. Is the starting date in an easily identifiable location, such as the top of the column?
Where the starting date is the date of the first payment?
2. How do you determine the amount of increase? Relative to the first payment? According to a fixed schedule applicable to all? A mix of both?

Have a nice day! :D
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Fri Dec 16, 2011 4:41 am

Hi,

I have worked out what I had in mind. Here it is:
Code: Select all
=C$5+(INT(DATEDIF(C$3,$B7,"m")/6)*ROUND(C$5 * 0.1,0))


This formula takes the amount of the first payment and adds increase to it.
The increases are calculated based on the number of increase dates gone since the first payment and an assumed formula for calculating each increase. This formula can be replaced by any other.

C3 has a starting date. From this payment dates are calculated using these formulas:
Code: Select all
=EOMONTH(C$3,0)

to calculate the first end of the month after C3. Any other day of the month could be calculated instead.
Code: Select all
=EDATE(B5,1)

Adds one month to the date in the row above it.

C5 has the amount of the first payment. To set up a payment schedule you only have to enter this amount and C3, then copy down formulas and dates. I don't like the formula for calculating the increase:
Code: Select all
=ROUND(C$5 * 0.1,0)

Even if this were the formula it shouldn't be included in the calculation. I would have preferred to put it at the top of the column. The reason why I didn't is that this might not be a fixed amount. If the increase is dynamic, at least part of it must be in the monthly formula. So, the present formula serves as a placeholder.

The entire formula, proof of testing, and development notes are in the attached spreadsheet. I hope you will be able to take it from here. Have fun! :D
Regards,
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: How to repeat a cell depending on a value in another cel

Postby pbcaer » Sun Dec 18, 2011 10:01 am

well nice job.. thanks ..

but still it's lil bit different than what I need..

I ll try to explain
6th and 12th months are increase months. If a customer joins by 2. month then there ll be 4 months for em without increase.. and then every sixth months it will increase..
I have a list goes like this:
ColA ColB ColC
1m 4m 500
5m 10m 500+increase
11m 16m 500+increase1+increase2
  • 0

pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Mon Dec 19, 2011 4:08 am

Hi,
I see what you mean, but can't get at the error on the iPad I'm on now. Look at the sequence in the column for calculating 0. This is the multiplicator determining the increase and it turns precisely on 7 and 1 the way it should. So, I suspect that the formula was pasted into the worksheet in the wrong row. I shall have another look when I'm on a PC next. I expect to find that the formula (copied from my post)
Code: Select all
=C$5+(INT(DATEDIF(C$3,$B7,"m")/6)*ROUND(C$5 * 0.1,0))

wasn't posted into row 7 as is indicated by the reference to $B7 but to row 10 and then copied down to subsequent cells. Therefore the increase appears to be made in October and March instead of July and January.

Actually, my repairing the posted worksheet in this regard won't solve your problem. When you deploy the formula you will still have to point "C$3", "C$5" and "$B7" at where your dates are on your own sheet. :D
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: How to repeat a cell depending on a value in another cel

Postby pbcaer » Mon Dec 19, 2011 8:53 am

yes there s a problem which seems unsolutiable.. if it changed by every six months it d be easy..

I am trying to have a general rule to solve this problem. I manuallyy did a list.. but if I could solve, d be great.

There two main issues, first the initial difference is between 0 n 5, whereas the others are 6.

What I need is, imagine I have a number lets say "2" in f3 and another "5" in f4.. And I have a value in a3 "100", and another in b3 "105"..

In d10 I want a list to appear, with a code " repeat the value in A3 as many as F3 ", and in e11 " repeat the value in B3 as many as F4 ". Then I will have two lists which I can refer to in another list.. And I can hide the first two clone lists..

The problem is I cannot get the excel repeat a value in a cell according to another cell. clearer now ?

Thanks
  • 0

pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Tue Dec 20, 2011 3:21 am

Hi,

Now we have the situation where you keep restating your wish and I keep saying that I already gave you the solution. How could this happen? Well, it would not have happened if you would make your question clear. I asked some questions in return initially and then rushed to a solution without awaiting your reply - which you didn't give.

Now you state that you want two lists that you don't need in order to make a third list. I think, the formula I worked out prepares the third list. Why don't you show me at least one of two things:-
Either a list - in an Excel sheet (xls format, please) that looks like the list you want AND has the reference points in it, namely the starting date and monthly dates.
Or, a list prepared with my formula with comments added by you showing where it goes wrong. You can use the sheet I posted. Just write the results you want next to those the list provides.

I think it would be possible to construct something based on your most recent description. But, forgive me, I feel reluctant to construct two lists you don't need while I believe that I already provided the final answer. :D

It now occurs to me that you might actually need the two lists you want to hide. The last time I acted on such a late impuls I did a lot of work you claim to be useless. So, this time I'll wait for your response. Of course, if you do need the two lists, they should have a particular format that you should clarify. The values would have to be separated by commas or semicolons or spaces, perhaps. But most importantly, such a list, in a single cell, can't be anything but a string. So, to extract a value from the list you would have to first extract the element and then convert it to a number. That would be quite some formula - more likely code. Do you already have that?
Regards,
Sisyphus
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Next

Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 38 guests

cron