Challenge for you clever people, duty cycle in Excel!

[Jacc]

Hello people!

I'm reasonable skilled in Excel and for the first time in forever I'm stuck.
I want to create a column with ones and zeros in a "duty cycle" fashion. By changing two parameters I want to be able to change the duty cycle and the period of the ones. If someone manage to do this with formulas I will be impressed.

For instance:
11100000001110000000
This would be duty cycle 30% and period 10.

11111000000000000000111110000000000000000
This would be duty cycle 25% and period 20.

Ultimately I want to decide the length of the column myself but if that's an obstacle then I can adapt.

As a plan B I'm trying to write a macro which will fill in this column for me but it turns out I'm struggling with that to.

 

[MintJulep]

=IF(ROW(A1)<B$1,IF(ROW(A1)<=(B$1*C$1),1,0))

enter in A1 and copy down as far as you care.

Period goes in B1. Duty cycle goes in C1 as a decimal.

 

[IRstuff]

Something like
=IF(MOD(ROW(),Num_rows/Num_cycles)<Duty_cycle*Num_rows/Num_cycles,1,0)

TTFN

FAQ731-376

 

[melone]

Will you accept a VBA solution?

 

[melone]

A1(DC) = 50%
B1(Per) = 10
C1 = "=LEFT(ALL_1s,DC*Per) & LEFT(ALL_0s, (1-DC)*Per)"
D1(ALL_1s)= 1111111111
E1(ALL_0s)= 0000000000


This is a pretty horrible solution, but it is a start, right?

VBA is probably the best / easiest / fastest way to solve this....

 

[Jacc]

Damn! You guys are fast!

>MintJulep
Your formula looks very neat but it would only run for one period then it returned FALSE.

>IRstuff
I haven't tried your formula yet but I will as soon as I get some sleep.

I'll be back within 24 hours. I really appreciate your answers! This is very cool!

 

[MintJulep]

You didn't say you needed more than one period.

 

[MintJulep]

=IF(MOD(ROW(A1),B$1)<B$1,IF(MOD(ROW(A1),B$1)<=(B$1*C$1),IF(MOD(ROW(A1),B$1)=0,0,1),0))

 

[Jacc]

>MintJulep
"...copy down as far as you care." Oh yeah?
There was more than one period in my examples! What good is it to talk about periods if there is only one? I have a feeling you are a lot smarter than this...

Well, I couldn't sleep so I went back to the drawing board.
MintJulep led me on the right track using the Row command.
My solution is:
=IF(MOD(ROW(A1);B$1)<(B$1*C$1);1;0)
Period goes in B1.
Duty cycle goes in C1 as a decimal.

Thanks for your input!

 

[Jacc]

You beat me to it, MintJulep! I appreciate your input.

 

[MintJulep]

What good is it to talk about periods if there is only one?

If its periodic, what's the need to define it more than once?

 

[Jacc]

Good point! I see you are into heat transfer. You wouldn't happend to have any experience with hydraulic fan drives? I realize that it can be a far way from heat transfer to hydraulic fan drives but I might as well ask.

 

[MintJulep]

Hydraulic fan drives? Not I. Try posting your question in forum404. I'm sure one of the regulars there will be able to help.