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

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