How to shorten formulas??

[dwest100]

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

=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!

[ScottO]

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" <[email protected]>
wrote in message
news:[email protected]...
|
| Is there a technique to shorten formulas such as this one?
|
|
=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!
|
|
| --
| dwest100
| -------------------------------------------------------------------
-----
| dwest100's Profile:
http://www.excelforum.com/member.php...o&userid=18864
| View this thread:
http://www.excelforum.com/showthread...hreadid=471673
|

[Roger Govier]

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+(K1>15)*2+(K1>40)*2+(K1>60)*2+(K1>70)*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" <[email protected]>
> wrote in message
> news:[email protected]...
> |
> | Is there a technique to shorten formulas such as this one?
> |
> |
> =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!
> |
> |
> | --
> | dwest100
> | -------------------------------------------------------------------
> -----
> | dwest100's Profile:
> http://www.excelforum.com/member.php...o&userid=18864
> | View this thread:
> http://www.excelforum.com/showthread...hreadid=471673
> |
>
>

[Roger Govier]

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+(K1>15)*2+(K1>40)*2+(K1>60)*2+(K1>70)*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" <[email protected]>
>> wrote in message
>> news:[email protected]...
>> |
>> | Is there a technique to shorten formulas such as this one?
>> |
>> |
>> =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!
>> |
>> |
>> | --
>> | dwest100
>> | -------------------------------------------------------------------
>> -----
>> | dwest100's Profile:
>> http://www.excelforum.com/member.php...o&userid=18864
>> | View this thread:
>> http://www.excelforum.com/showthread...hreadid=471673
>> |
>>
>>

[Roger Govier]

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+(K1>15)*2+(K1>40)*2+(K1>60)*2+(K1>70)*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(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!
>
>