Home 
Search 
Today's Posts 
#1




Infinite sum formula
Hi there.
I am looking for a formula that I can use for rows in a worksheet to calculate a total infinitely. Basically, I would like two cells in each row to add together to obtain a total for that row. For example: F6+G6=H6, F7+G7=H7, F8+G8=H8, etc. However, I would like the formula to continue on infinitely, no matter how many rows my worksheet will contain. Does anyone know a formula that would meet this criteria? Any help would be greatly appreciated! Thanks! Jen 
#2




Infinite sum formula
wrote...
.... Basically, I would like two cells in each row to add together to obtain a total for that row. For example: F6+G6=H6, F7+G7=H7, F8+G8=H8, etc. However, I would like the formula to continue on infinitely, no matter how many rows my worksheet will contain. .... You could select H6:H65536, type =IF(COUNT(F6,G6,F6+G6)1,F6+G6,"") hold down a [Ctrl] key and press [Enter] to fill this formula into H6:H65536, but this would be grossly wasteful of your computer's resources. The alternative would be to use a Change event handler that fills the formula (R1C1style) =RC[2]+RC[1] in column H for every row in which column F or G contains a number and their sum evaluates to a number. Do you want to use VBA? 
#3




Infinite sum formula
In H6 enter =IF(AND(F6="",(G6="")),"",F6+G6)
Drag/copy down Column G as far as you wish although Excel has no "infinite" range. You are limited to the number of rows in your version. Cells will look blank until Columns F and G are filled. Gord Dibben MS Excel MVP On 1 May 2007 14:04:47 0700, wrote: Hi there. I am looking for a formula that I can use for rows in a worksheet to calculate a total infinitely. Basically, I would like two cells in each row to add together to obtain a total for that row. For example: F6+G6=H6, F7+G7=H7, F8+G8=H8, etc. However, I would like the formula to continue on infinitely, no matter how many rows my worksheet will contain. Does anyone know a formula that would meet this criteria? Any help would be greatly appreciated! Thanks! Jen 
#4




Infinite sum formula
On May 1, 4:34 pm, Harlan Grove wrote:
wrote... ...Basically, I would like two cells in each row to add together to obtain a total for that row. For example: F6+G6=H6, F7+G7=H7, F8+G8=H8, etc. However, I would like the formula to continue on infinitely, no matter how many rows my worksheet will contain. ... You could select H6:H65536, type =IF(COUNT(F6,G6,F6+G6)1,F6+G6,"") hold down a [Ctrl] key and press [Enter] to fill this formula into H6:H65536, but this would be grossly wasteful of your computer's resources. The alternative would be to use a Change event handler that fills the formula (R1C1style) =RC[2]+RC[1] in column H for every row in which column F or G contains a number and their sum evaluates to a number. Do you want to use VBA? Thank you very much. I am now running into another problem. I entered the formula per your instructions, but when I click print, Excel wants to print every single cell that contains the formula, which is thousands of pages. I know that I can highlight a selection to print to avoid printing all those pages, however I am setting up this spreadsheet for someone that is not very familar with Excel. I am afraid that there will be times she will forget to highlight the selection and end up printing thousands of pages. Is there a way I can get Excel to print only the rows that contain actual numbers and not just formulas with no numbers entered. Does that make sense? I appreciate all your help. 
#5




Infinite sum formula
On May 2, 12:49 pm, wrote:
.... Thank you very much. I am now running into another problem. I entered the formula per your instructions, but when I click print, Excel wants to print every single cell that contains the formula, which is thousands of pages. . . . I said there were drawbacks to entering such formulas in every row, but apparently you didn't believe me or expected me to enumerate all the portential problems. Sorry, didn't think I needed to. . . I know that I can highlight a selection to print to avoid printing all those pages, however I am setting up this spreadsheet for someone that is not very familar with Excel. I am afraid that there will be times she will forget to highlight the selection and end up printing thousands of pages. Is there a way I can get Excel to print only the rows that contain actual numbers and not just formulas with no numbers entered. Does that make sense? Yes, but if your friend/user EVER does use the File Print Area menu command, the following workaround will be eliminated. If the worksheet name were WSN, the range containing these formulas were H6:H65536, and you wanted columns A through J in the printout, you need to define the WORKSHEETlevel name WSN!Print_Area referring to the formula =WSN!$A$1:INDEX(WSN!$J$6:$J$65536,MATCH(2,1/(WSN!$H$6:$H$65536<""))) This sets the WSN worksheet's Print_Area range to the dynamic range beginning in cell A1 and ending in column J on the last row in col H that's not equal to "". To repeat: if your friend/user EVER sets the print area on this worksheet to anything else, this workaround is fubar, gone, history, toast, no longer functional. There's no way to prevent this. While Excel prevents users from changing NORMAL defined names in protected worksheets, it doesn't prevent users from changing the print area in protected worksheets. There are VBA workarounds to restore the dynamic print area formula, but I'd guess you want to avoid macros for this user. 
#6




Infinite sum formula
Awesome! That worked. Thanks so much!

Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Infinite loop using Worksheet_Calculate  Excel Discussion (Misc queries)  
infinite rows?  Charts and Charting in Excel  
For Next Infinite Loop  Excel Discussion (Misc queries)  
Infinite Updating Column  Excel Worksheet Functions  
How to calculate NPV of an infinite series?  Excel Discussion (Misc queries) 