|
How to shorten formulas??
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! |
| All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com