Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Adding a KeyID column for sorting | New Users to Excel |