Looking for a formula to perform this additions
"Ayo" wrote:
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number part
of
H308 and the number part of J308)
If the number before the letter is always one digit, then:
G338: =--left(G308,1)
H338: =--left(H308,1)
I338: =G338 + left(I308,1)
J338: =H338 + left(J308,1)
Alternatively, if the number before the letter might have multiple digits,
but the right-hand text is always a single letter, then replace LEFT(G308,1)
with LEFT(G308,LEN(308)-1), and similarly for the other LEFT expressions.
I got a formula that would do the additions for me, array-entered,
=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))
if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.
I don't see how that formula relates to the first part of the question, so I
don't know how to fix it so that it accommodates the As and Ps.
----- original message -----
"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,
=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))
if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.
Any ideas. Thanks
|