Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an Excel Function that can be used in lieu of this fo
Thanks Ron. Ithink this is what I was looking for except for one small issue
that I didn't mention before, the values in G5 I5 K5 M5 etc are concantenated with "A". For example G5=4A and I5=3A so now I have to figure out how to modify the formular you gave me to ignore the As and just add the numbers. I know it involves using MID() and LEN() but I can't figure out where they go in the formula. Any ideas? Thanks "Ron Rosenfeld" wrote: On Mon, 13 Jul 2009 10:53:01 -0700, Ayo wrote: I need to add value in every other cell in a row. For example, let say I have these ranges: G6, I6, K6, M6 ..... and H6, J6, L6, N6 ...... What I am doing right now is: G6=SUM($F5,G5) I6=SUM($F5,G5,I5) K6=SUM($F5,G5,I5,K5) M6=SUM($F5,G5,I5,K5,M5) H6=SUM($F5,H5) J6=SUM($F5,H5,J5) L6=SUM($F5,H5,J5,L5) N6=SUM($F5,H5,J5,L5,N5) I am looking for a short-cut to having to do this for (39x2) cells per row and 12 rows total. I would appreciate it a great deal if someone can tell me if the is an alternate way to do it that doesn't involve this tedious work. Thanks Ayo Try this: This formula must be **array-entered**: G6: =$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. --ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an Excel Function that can be used in lieu of this fo
Ayo-
If every target value is followed by the character "A", you can use the following formula to pull it apart: Example located in Cell G5: "4A" Put this formula in H5 =VALUE(LEFT(G5,FIND("A", G5)-1)) The FIND locates the "A" so that your leading number can be any number of digits. LEFT pulls everything to the LEFT of that location VALUE changes the text "4" back into a number so you can add it to other numbers You'd have to play around with it to figure out how to use it in the array formula Ron provided (it would only go in the last part of the expression, $G5:G5) HTH, Keith "Ayo" wrote: Thanks Ron. Ithink this is what I was looking for except for one small issue that I didn't mention before, the values in G5 I5 K5 M5 etc are concantenated with "A". For example G5=4A and I5=3A so now I have to figure out how to modify the formular you gave me to ignore the As and just add the numbers. I know it involves using MID() and LEN() but I can't figure out where they go in the formula. Any ideas? Thanks "Ron Rosenfeld" wrote: On Mon, 13 Jul 2009 10:53:01 -0700, Ayo wrote: I need to add value in every other cell in a row. For example, let say I have these ranges: G6, I6, K6, M6 ..... and H6, J6, L6, N6 ...... What I am doing right now is: G6=SUM($F5,G5) I6=SUM($F5,G5,I5) K6=SUM($F5,G5,I5,K5) M6=SUM($F5,G5,I5,K5,M5) H6=SUM($F5,H5) J6=SUM($F5,H5,J5) L6=SUM($F5,H5,J5,L5) N6=SUM($F5,H5,J5,L5,N5) I am looking for a short-cut to having to do this for (39x2) cells per row and 12 rows total. I would appreciate it a great deal if someone can tell me if the is an alternate way to do it that doesn't involve this tedious work. Thanks Ayo Try this: This formula must be **array-entered**: G6: =$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a semicolon in lieu of ALT Enter | Excel Worksheet Functions | |||
Top Row on Excel has changed to 1, 2, 3... in lieu of A, B, C? | New Users to Excel | |||
SUMPRODUCT() challenge - How to integrate a string value in lieu of a value in a cell ..... | Excel Discussion (Misc queries) | |||
Excel 2007 - Using a Named Ranges in lieu of Cell References | Excel Programming | |||
What does a "plus" cursor mean (in lieu of the pointer?)? thanks | Excel Worksheet Functions |