A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Infinite sum formula



 
 
Thread Tools Display Modes
  #1  
Old May 1st 07, 10:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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

Ads
  #2  
Old May 1st 07, 10:34 PM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,230
Default 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 (R1C1-style)

=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  
Old May 1st 07, 11:05 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,912
Default 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  
Old May 2nd 07, 08:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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 (R1C1-style)
>
> =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  
Old May 2nd 07, 10:11 PM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,230
Default 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
WORKSHEET-level 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  
Old May 3rd 07, 08:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Infinite sum formula

Awesome! That worked. Thanks so much!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Infinite loop using Worksheet_Calculate Parker Excel Discussion (Misc queries) 2 November 3rd 06 12:25 PM
infinite rows? Pauline Charts and Charting in Excel 1 September 27th 06 08:16 AM
For Next Infinite Loop Naji Excel Discussion (Misc queries) 5 January 13th 06 06:56 PM
Infinite Updating Column murphyslaw18 Excel Worksheet Functions 2 January 13th 06 02:39 AM
How to calculate NPV of an infinite series? Mike Excel Discussion (Misc queries) 2 September 15th 05 06:10 PM


All times are GMT +1. The time now is 11:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.