Is there a technique to shorten formulas such as this one?

=IF(K7=TIME(0,1,30)),J7,IF(AND(K7TIME(0,1,45),K7< =TIME(0,2,10)),J7+2,IF(AND(K7TIME(0,2,10),K7<=TIM E(0,2,30)),J7+4,IF(AND(K7TIME(0,2,30),K7<=TIME(0, 2,40)),J7+6,IF(K7TIME(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!


--
dwest100
------------------------------------------------------------------------
dwest100's Profile: http://www.excelforum.com/member.php...o&userid=18864
View this thread: http://www.excelforum.com/showthread...hreadid=471673

With the formula example given, you might like to create a table of
the various time span answers, and then use one of the lookup
functions or an index/match combination to select the relevant
response.
Rgds,
ScottO

"dwest100"
wrote in message
...
|
| Is there a technique to shorten formulas such as this one?
|
|
=IF(K7=TIME(0,1,30)),J7,IF(AND(K7TIME(0,1,45),K7< =TIME(0,2,10)),J7+2
,IF(AND(K7TIME(0,2,10),K7<=TIME(0,2,30)),J7+4,IF( AND(K7TIME(0,2,30)
,K7<=TIME(0,2,40)),J7+6,IF(K7TIME(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!
|
|
| --
| dwest100
| -------------------------------------------------------------------
-----
| dwest100's Profile:
http://www.excelforum.com/member.php...o&userid=18864
| View this thread:
http://www.excelforum.com/showthread...hreadid=471673
|

Hi Scott

One way would be to use a helper cell and convert all time intervals into
decimal minutes. I used cell K1 and entered
=(K7-TIME(1,30,0))*24*60
Excel stores times as fractions of a day so multiplying by 24 gives hours,
and further multiplying by 60 gives minutes.

Then your time intervals after 1:30 are 15 mins, 25, 20, 10 or cumulatively
they are 15, 40, 60 and 70 and you are adding to the value in J7 either
2,4,6 or 10
so the formula can be simplified to

=(K1=0)*J7+(K115)*2+(K140)*2+(K160)*2+(K170)* 4

where the values within the brackets will either be True or False.

Multiplying them by a number, coerces them to either 1 for True or 0 for
False so if the calculated time in minutes is greater than or equal to 0,
then it must be at least 1:30 and we use the value in J7.

The rest of the formula then adds the incremental value, depending upon how
far past 1:30 we are.


Regards

Roger Govier


ScottO wrote:
With the formula example given, you might like to create a table of
the various time span answers, and then use one of the lookup
functions or an index/match combination to select the relevant
response.
Rgds,
ScottO

"dwest100"
wrote in message
...
|
| Is there a technique to shorten formulas such as this one?
|
|
=IF(K7=TIME(0,1,30)),J7,IF(AND(K7TIME(0,1,45),K7< =TIME(0,2,10)),J7+2
,IF(AND(K7TIME(0,2,10),K7<=TIME(0,2,30)),J7+4,IF( AND(K7TIME(0,2,30)
,K7<=TIME(0,2,40)),J7+6,IF(K7TIME(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!
|
|
| --
| dwest100
| -------------------------------------------------------------------
-----
| dwest100's Profile:
http://www.excelforum.com/member.php...o&userid=18864
| View this thread:
http://www.excelforum.com/showthread...hreadid=471673
|

My Apologies Scott

I clicked on the wrong message. This was meant for dWest
Regards

Roger Govier


Roger Govier wrote:
Hi Scott

One way would be to use a helper cell and convert all time intervals
into decimal minutes. I used cell K1 and entered
=(K7-TIME(1,30,0))*24*60
Excel stores times as fractions of a day so multiplying by 24 gives
hours, and further multiplying by 60 gives minutes.

Then your time intervals after 1:30 are 15 mins, 25, 20, 10 or
cumulatively they are 15, 40, 60 and 70 and you are adding to the value
in J7 either 2,4,6 or 10
so the formula can be simplified to

=(K1=0)*J7+(K115)*2+(K140)*2+(K160)*2+(K170)* 4

where the values within the brackets will either be True or False.

Multiplying them by a number, coerces them to either 1 for True or 0 for
False so if the calculated time in minutes is greater than or equal to
0, then it must be at least 1:30 and we use the value in J7.

The rest of the formula then adds the incremental value, depending upon
how far past 1:30 we are.


Regards

Roger Govier


ScottO wrote:

With the formula example given, you might like to create a table of
the various time span answers, and then use one of the lookup
functions or an index/match combination to select the relevant
response.
Rgds,
ScottO

"dwest100"
wrote in message
...
|
| Is there a technique to shorten formulas such as this one?
|
|
=IF(K7=TIME(0,1,30)),J7,IF(AND(K7TIME(0,1,45),K7< =TIME(0,2,10)),J7+2
,IF(AND(K7TIME(0,2,10),K7<=TIME(0,2,30)),J7+4,IF( AND(K7TIME(0,2,30)
,K7<=TIME(0,2,40)),J7+6,IF(K7TIME(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!
|
|
| --
| dwest100
| -------------------------------------------------------------------
-----
| dwest100's Profile:
http://www.excelforum.com/member.php...o&userid=18864
| View this thread:
http://www.excelforum.com/showthread...hreadid=471673
|

Hi

One way would be to use a helper cell and convert all time intervals into
decimal minutes. I used cell K1 and entered
=(K7-TIME(1,30,0))*24*60
Excel stores times as fractions of a day so multiplying by 24 gives hours,
and further multiplying by 60 gives minutes.

Then your time intervals after 1:30 are 15 mins, 25, 20, 10 or cumulatively
they are 15, 40, 60 and 70 and you are adding to the value in J7 either
2,4,6 or 10
so the formula can be simplified to

=(K1=0)*J7+(K115)*2+(K140)*2+(K160)*2+(K170)* 4

where the values within the brackets will either be True or False.

Multiplying them by a number, coerces them to either 1 for True or 0 for
False so if the calculated time in minutes is greater than or equal to 0,
then it must be at least 1:30 and we use the value in J7.

The rest of the formula then adds the incremental value, depending upon how
far past 1:30 we are.

Regards

Roger Govier


dwest100 wrote:
Is there a technique to shorten formulas such as this one?

=IF(K7=TIME(0,1,30)),J7,IF(AND(K7TIME(0,1,45),K7< =TIME(0,2,10)),J7+2,IF(AND(K7TIME(0,2,10),K7<=TIM E(0,2,30)),J7+4,IF(AND(K7TIME(0,2,30),K7<=TIME(0, 2,40)),J7+6,IF(K7TIME(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!