Adding a column
 

Can someone give me a formula (or whatever its called) to add a column
that would be set like this:

A1=3000
A2=6200
A3=2250 (1625)
A4=10500
A5=6600 (4566)
etc.

If there is only one number in cell it is counted, if 2, then the one
in parentheses is the only one of the 2 counted.

Thanx


--
roger_home
------------------------------------------------------------------------
roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434
View this thread: http://www.excelforum.com/showthread...hreadid=503851

Adding a column
 

Hi roger,
This is an array formula so commit with Ctrl+Shift+Enter, also edit the
addresses so that all of your column A data are included rather than
the first five only, but don't use A:A.

=SUM(IF(ISERROR(FIND("(",A1:A5)),A1:A5,VALUE(MID(A 1:A5,FIND("(",A1:A5)
+1,LEN(A1:A5)-(FIND("(",A1:A5) +1)))))

Ken Johnson

Adding a column
 

Sorry, newbie here, you've lost me in the not using the range
A1:A5...how do I do it without A:A?


--
roger_home
------------------------------------------------------------------------
roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434
View this thread: http://www.excelforum.com/showthread...hreadid=503851

Adding a column
 

Hi Roger,
It's an array formula and they don't work on entire column (or row)
addresses such as A:A.
You can however us A1:A65535 which is all of column A bar the last row.
Or, if you know the last row in column A that your data uses then you
can use that row number in the address. eg last value is in row 27500
then use A1:A27500, but it's probably easier to use the "all bar last
row" option
Ken Johnson

Adding a column
 

Unless you like to drink coffee while you watch your worksheet calculate, it
would not be advisable to address more rows than you need to when using
array formulas.

--
Regards,
Tom Ogilvy



"Ken Johnson" wrote in message
oups.com...
Hi Roger,
It's an array formula and they don't work on entire column (or row)
addresses such as A:A.
You can however us A1:A65535 which is all of column A bar the last row.
Or, if you know the last row in column A that your data uses then you
can use that row number in the address. eg last value is in row 27500
then use A1:A27500, but it's probably easier to use the "all bar last
row" option
Ken Johnson

Adding a column
 

Nothing like testimony to bolster the point: From a very recent post
subject Help Needed with Sumproduct

Yeah, I just noticed that I defaulted from rows 2 to 65,535. I limited
the scope and the refreshes sped up, although calculations still take
longer than I would think is necessary.

Even though this user is using Sumproduct and sumproduct does not require
array entry, it is nonetheless an array formula.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Unless you like to drink coffee while you watch your worksheet calculate,
it
would not be advisable to address more rows than you need to when using
array formulas.

--
Regards,
Tom Ogilvy



"Ken Johnson" wrote in message
oups.com...
Hi Roger,
It's an array formula and they don't work on entire column (or row)
addresses such as A:A.
You can however us A1:A65535 which is all of column A bar the last row.
Or, if you know the last row in column A that your data uses then you
can use that row number in the address. eg last value is in row 27500
then use A1:A27500, but it's probably easier to use the "all bar last
row" option
Ken Johnson

Adding a column
 

Hi Tom,
Thanks again, my experience of array formulas is very limited. I've
never actually used one myself. It's nice to know the pitfalls. Let's
hope Roger does not heed my recommended "easy" option!
Ken Johnson