Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
I have previously used the "choose" function for sum functions that would sum
a data range for a month-to-date display. Recently we have found that one of the updates we have ran has limited the number of agruments that the choose function supports has been limited to 28 arguments. The problem I face is 29, 30, and 31 day months become a problem. We have not been able to uninstall the update (assuming that this change was made with a Service Pack update). Does anybody have suggestions on how to accomplish the same result with a different formula? A couple of items to help try and explain. On the 14th day I would like to see a month to date total from the 1st to the 14th. On the 30th day I would like to see a month to date total from the 1st to the 30th. These would be simple to fix with a sum function, however, in the same sheet I also preload daily data from the prior year, so the sum function would always show the total from the 1st to the end of the month. I also have a date key that we use to drive which day's data is shown so that it is possible for me to quickly change the data that is displayed to any day I select. this made it possible for me to see data through the 14th of the month, even though data had been entered say to the 21st or beyond. If it is any help, here is an example of how the formula was written prior to the limitation of the number of arguments in the choose function (forgive the length): =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) The reference to $D$3 is to the date key that displays which number of day we are in for the month (or which day I would like to see totals through). Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
One way
=IF($D$3=0,0,SUM(U7:INDEX(U7:AY7,$D$3))) -- Regards, Peo Sjoblom "dkcpa" wrote in message ... I have previously used the "choose" function for sum functions that would sum a data range for a month-to-date display. Recently we have found that one of the updates we have ran has limited the number of agruments that the choose function supports has been limited to 28 arguments. The problem I face is 29, 30, and 31 day months become a problem. We have not been able to uninstall the update (assuming that this change was made with a Service Pack update). Does anybody have suggestions on how to accomplish the same result with a different formula? A couple of items to help try and explain. On the 14th day I would like to see a month to date total from the 1st to the 14th. On the 30th day I would like to see a month to date total from the 1st to the 30th. These would be simple to fix with a sum function, however, in the same sheet I also preload daily data from the prior year, so the sum function would always show the total from the 1st to the end of the month. I also have a date key that we use to drive which day's data is shown so that it is possible for me to quickly change the data that is displayed to any day I select. this made it possible for me to see data through the 14th of the month, even though data had been entered say to the 21st or beyond. If it is any help, here is an example of how the formula was written prior to the limitation of the number of arguments in the choose function (forgive the length): =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) The reference to $D$3 is to the date key that displays which number of day we are in for the month (or which day I would like to see totals through). Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
Hasn't CHOOSE always been limited to 29? I don't think that there has been
any recent relevant update. If you Google you will find articles from a long while ago which refer to the 29 limit (and of course Excel help says that 29 is the limit). As for the way round your proble, well, you could test for large or small values in D3 and go to one or other of 2 separate CHOOSE functions, but why not just use: =SUM(OFFSET(U7,0,0,1,$D$3)) ? -- David Biddulph "dkcpa" wrote in message ... I have previously used the "choose" function for sum functions that would sum a data range for a month-to-date display. Recently we have found that one of the updates we have ran has limited the number of agruments that the choose function supports has been limited to 28 arguments. The problem I face is 29, 30, and 31 day months become a problem. We have not been able to uninstall the update (assuming that this change was made with a Service Pack update). Does anybody have suggestions on how to accomplish the same result with a different formula? A couple of items to help try and explain. On the 14th day I would like to see a month to date total from the 1st to the 14th. On the 30th day I would like to see a month to date total from the 1st to the 30th. These would be simple to fix with a sum function, however, in the same sheet I also preload daily data from the prior year, so the sum function would always show the total from the 1st to the end of the month. I also have a date key that we use to drive which day's data is shown so that it is possible for me to quickly change the data that is displayed to any day I select. this made it possible for me to see data through the 14th of the month, even though data had been entered say to the 21st or beyond. If it is any help, here is an example of how the formula was written prior to the limitation of the number of arguments in the choose function (forgive the length): =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) The reference to $D$3 is to the date key that displays which number of day we are in for the month (or which day I would like to see totals through). Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
Use the INDEX function instead.
=SUM(U7:INDEX(U7:AY7,D3+1)) -- Biff Microsoft Excel MVP "dkcpa" wrote in message ... I have previously used the "choose" function for sum functions that would sum a data range for a month-to-date display. Recently we have found that one of the updates we have ran has limited the number of agruments that the choose function supports has been limited to 28 arguments. The problem I face is 29, 30, and 31 day months become a problem. We have not been able to uninstall the update (assuming that this change was made with a Service Pack update). Does anybody have suggestions on how to accomplish the same result with a different formula? A couple of items to help try and explain. On the 14th day I would like to see a month to date total from the 1st to the 14th. On the 30th day I would like to see a month to date total from the 1st to the 30th. These would be simple to fix with a sum function, however, in the same sheet I also preload daily data from the prior year, so the sum function would always show the total from the 1st to the end of the month. I also have a date key that we use to drive which day's data is shown so that it is possible for me to quickly change the data that is displayed to any day I select. this made it possible for me to see data through the 14th of the month, even though data had been entered say to the 21st or beyond. If it is any help, here is an example of how the formula was written prior to the limitation of the number of arguments in the choose function (forgive the length): =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) The reference to $D$3 is to the date key that displays which number of day we are in for the month (or which day I would like to see totals through). Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
I'm afraid I have to disappoint you; the number of arguments for any function is limited to 30 (before Excel 2007), so minus the
first one there are only 29 SUM formulas to be chosen from. This has always been the case. It would be easier to do this: In U8, enter this formula: =SUM($U$7:U7) Copy to the right to AY8. Now your formula to get the right sum , with the day in A1, is: =INDEX(U8:AY8,1,A1) -- Kind regards, Niek Otten Microsoft MVP - Excel "dkcpa" wrote in message ... |I have previously used the "choose" function for sum functions that would sum | a data range for a month-to-date display. Recently we have found that one of | the updates we have ran has limited the number of agruments that the choose | function supports has been limited to 28 arguments. The problem I face is | 29, 30, and 31 day months become a problem. We have not been able to | uninstall the update (assuming that this change was made with a Service Pack | update). Does anybody have suggestions on how to accomplish the same result | with a different formula? | | A couple of items to help try and explain. | | On the 14th day I would like to see a month to date total from the 1st to | the 14th. | On the 30th day I would like to see a month to date total from the 1st to | the 30th. | | These would be simple to fix with a sum function, however, in the same sheet | I also preload daily data from the prior year, so the sum function would | always show the total from the 1st to the end of the month. | | I also have a date key that we use to drive which day's data is shown so | that it is possible for me to quickly change the data that is displayed to | any day I select. this made it possible for me to see data through the 14th | of the month, even though data had been entered say to the 21st or beyond. | | If it is any help, here is an example of how the formula was written prior | to the limitation of the number of arguments in the choose function (forgive | the length): | | =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) | | The reference to $D$3 is to the date key that displays which number of day | we are in for the month (or which day I would like to see totals through). | | Thanks | |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
I wish I could tell you the origination of the sheet we use - it has been
around for years and years and was most likely a lotus version at one point. On some computers I can open and access the information that is computing on a 31 argument choose function, and on others I can't. Just recently the computers that are most frequently updated started to show the errors with the formulas, while the older ones would still process it. Just interesting to see that I could get a excel 03 to run a function that it didn't support for several years. Anyway, this has been great help and I can clean everything up and keep it running correctly forward. "Niek Otten" wrote: I'm afraid I have to disappoint you; the number of arguments for any function is limited to 30 (before Excel 2007), so minus the first one there are only 29 SUM formulas to be chosen from. This has always been the case. It would be easier to do this: In U8, enter this formula: =SUM($U$7:U7) Copy to the right to AY8. Now your formula to get the right sum , with the day in A1, is: =INDEX(U8:AY8,1,A1) -- Kind regards, Niek Otten Microsoft MVP - Excel "dkcpa" wrote in message ... |I have previously used the "choose" function for sum functions that would sum | a data range for a month-to-date display. Recently we have found that one of | the updates we have ran has limited the number of agruments that the choose | function supports has been limited to 28 arguments. The problem I face is | 29, 30, and 31 day months become a problem. We have not been able to | uninstall the update (assuming that this change was made with a Service Pack | update). Does anybody have suggestions on how to accomplish the same result | with a different formula? | | A couple of items to help try and explain. | | On the 14th day I would like to see a month to date total from the 1st to | the 14th. | On the 30th day I would like to see a month to date total from the 1st to | the 30th. | | These would be simple to fix with a sum function, however, in the same sheet | I also preload daily data from the prior year, so the sum function would | always show the total from the 1st to the end of the month. | | I also have a date key that we use to drive which day's data is shown so | that it is possible for me to quickly change the data that is displayed to | any day I select. this made it possible for me to see data through the 14th | of the month, even though data had been entered say to the 21st or beyond. | | If it is any help, here is an example of how the formula was written prior | to the limitation of the number of arguments in the choose function (forgive | the length): | | =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) | | The reference to $D$3 is to the date key that displays which number of day | we are in for the month (or which day I would like to see totals through). | | Thanks | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choose function in Excel 2003
Thank you for the help.
"Peo Sjoblom" wrote: One way =IF($D$3=0,0,SUM(U7:INDEX(U7:AY7,$D$3))) -- Regards, Peo Sjoblom "dkcpa" wrote in message ... I have previously used the "choose" function for sum functions that would sum a data range for a month-to-date display. Recently we have found that one of the updates we have ran has limited the number of agruments that the choose function supports has been limited to 28 arguments. The problem I face is 29, 30, and 31 day months become a problem. We have not been able to uninstall the update (assuming that this change was made with a Service Pack update). Does anybody have suggestions on how to accomplish the same result with a different formula? A couple of items to help try and explain. On the 14th day I would like to see a month to date total from the 1st to the 14th. On the 30th day I would like to see a month to date total from the 1st to the 30th. These would be simple to fix with a sum function, however, in the same sheet I also preload daily data from the prior year, so the sum function would always show the total from the 1st to the end of the month. I also have a date key that we use to drive which day's data is shown so that it is possible for me to quickly change the data that is displayed to any day I select. this made it possible for me to see data through the 14th of the month, even though data had been entered say to the 21st or beyond. If it is any help, here is an example of how the formula was written prior to the limitation of the number of arguments in the choose function (forgive the length): =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7 ),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM (U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U 7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7: AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN 7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7) ,SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),S UM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7)) The reference to $D$3 is to the date key that displays which number of day we are in for the month (or which day I would like to see totals through). Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can 0 be used in the Choose function | Excel Worksheet Functions | |||
what function do I use for excel to choose the 2nd highest number | Excel Worksheet Functions | |||
Choose Function | Excel Discussion (Misc queries) | |||
CHOOSE function | Excel Discussion (Misc queries) | |||
CHOOSE Function | Excel Worksheet Functions |