How to shorten formulas?? techniques?? 1

[dwest100]

Is there a way to shorten this formula??

=IF(K7=TIME(0,1,30)),J7,IF(AND(K7>TIME(0,1,45),K7<=TIME(0,2,10)),J7+2,IF(AND(K7>TIME(0,2,10),K7<=TIME(0,2,30)),J7+4,IF(AND(K7>TIME(0,2,30),K7<=TIME(0,2,40)),J7+6,IF(K7>TIME(0,2,40),J7+10))))))

I'm always creating these spaghetti style formulas and I'd like to learn some techniques to shorten them.

Any advice or recommendations would be greatly appreciated!

 

[xlbo]

to be honest, best bet is to write out your conditions and then order them logically so if 1 occurs, you don't need to test for it again

In this case however, I don't think the formula can be shortened like that - in these cases, it is better to use a VLOOKUP formula and a lookup grid to return the results so, instead of your formula, you would have a range that contains:

01:30:00 =J7
01:45:00 =J7+2
02:10:00 ...etc etc

you then use a formula like

=vlookup(K7,LookupRange,2,TRUE)

the TRUE argument makes vlookup search for a NEAR match rather than an exact match - have a look at the help files for more specifics

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question