Is there an Excel Function that can be used in lieu of this formul

[Ayo]

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

 

[ker_01]

Ayo-

I can't claim to fully understand, because the first formula
(G6=SUM($F5,G5)) are two consecutive columns.

However, it appears that each of your formulas is the same as the previous
formula, plus one number. Instead of going to VBA programming, why not just
use the previous sum, plus one new number? e.g.:

I6=SUM(G6,I5)
K6=SUM(I6,K5)
M6=SUM(K6,M5)

since this is a relative formula, you should be able to just copy/paste it
into all your target cells and have it update appropriately.

HTH,
Keith

 

[Ron Rosenfeld]

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

 

[JLGWhiz]

If I understood the page layout a little better, I could probably cover the
whole matrix. Try this on a copy of your sheet and see if it does what you
want for the rows 5 an 6.
If that works OK, then maybe we can get it to work on down the page by
defining how the rows are set up.

Sub mtrx()
Dim sh As Worksheet, i As Long, j As Long
Set sh = ActiveSheet

j = 10
Cells(6, 8) = Cells(5, 6) + Cells(5, 8)
For i = 10 To 66 Step 2
sh.Cells(6, i) = sh.Cells(6, j - 2).Value + sh.Cells(5, j).Value
j = j + 2
Next

End Sub

 

[Ayo]

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

 

[ker_01]

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