PDA

View Full Version : Solved: Formula help please!



lacviet2005
08-21-2007, 02:13 PM
Hi all gurus,
I'll try my best to explain the problem, but please see the attached worksheet to get a better idea.

I have a worksheet with cycle from 1 to 13 (combo box), every cycle has 3 weeks (ie, 1st, 2nd, and 3rd by using radio buttons). Now I want to calculate the number of days.
For example:
Cycle 1: Week: 1st = Day 0
2nd = Day 7
3rd = Day 14

Cycle 2: Week: 1st = Day 21
2nd = Day 28
3rd = Day 35

Cycle 3: Week: 1st = Day 42
2nd = Day 49
3rd = Day 56
etc... :bug:
I got it to work for cycle 1 and 2, but my formula is too messy and i'm stuck. If you have any other ways to do this, please let me know.

Thanks so much.
LV

Bob Phillips
08-21-2007, 03:18 PM
=(CHOOSE(C6,-1,2,5,8,11,14,17,20)+G6)*7

p45cal
08-21-2007, 03:25 PM
=IF(C6=1,CHOOSE(G6,C6*0*7,C6*1*7,C6*2*7),IF(C6=2,CHOOSE(G6,(2+1)*7,(2+2)*7, (2+3)*7),IF(C6=3,CHOOSE(G6,(2+4)*7,(2+5)*7,(2+6)*7)))) but you have more than just 3 cycles.. there may be more to come. p45cal

added a minute or two later:
And Bob's just beaten me to it! Rats.
p45cal

rory
08-21-2007, 03:53 PM
Just as an alternative:
=(3*C6+G6-4)*7

lacviet2005
08-21-2007, 03:57 PM
Thanks xld. It works!

p45cal - I've tried something like your formula before, it's a lot of work, but thanks.

LV

p45cal
08-21-2007, 05:39 PM
Thanks xld. It works!

p45cal - I've tried something like your formula before, it's a lot of work, but thanks.

LV
Just in case you haven't recognised it, it's YOUR formula, corrected to work for the third cycle! You're right; it is too messy. p45cal :motz2: