Formula for adding back value?
 

-- Question-

I need a formula for adding back value of a cell?

Let us say...

B1 c1 d1 e1
f1 g1 h1
Inv. Amt. Check1# Chk1 Amt. Chk1Status Check2# Chk2 Amt. Chk2Status

Under I1 is the balance. When the word "bounce" is encoded under
"Chkstatus" the check amout should add back to balance.

Please help need it badly.
joromajr

Formula for adding back value?
 

Here's a formula that works

B3-SUM(D3,G3)+I2+(D3*--(E3="bounce"))+(G3*--(H3="bounce"))

the -- converts the logical test of E3="bounce" into a 1 or a 0

I2 is the beginning balance. Let me know if you want me to send you my test
sheet
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"joromajr" wrote:


-- Question-

I need a formula for adding back value of a cell?

Let us say...

B1 c1 d1 e1
f1 g1 h1
Inv. Amt. Check1# Chk1 Amt. Chk1Status Check2# Chk2 Amt. Chk2Status

Under I1 is the balance. When the word "bounce" is encoded under
"Chkstatus" the check amout should add back to balance.

Please help need it badly.
joromajr

Formula for adding back value?
 

In this case, the -- aren't necessary. Doing arithmetic (including multiplying)
will coerce those true/false to 1/0's, too.

=B3-SUM(D3,G3)+I2+(D3*(E3="bounce"))+(G3*(H3="bounce") )

M Kan wrote:

Here's a formula that works

B3-SUM(D3,G3)+I2+(D3*--(E3="bounce"))+(G3*--(H3="bounce"))

the -- converts the logical test of E3="bounce" into a 1 or a 0

I2 is the beginning balance. Let me know if you want me to send you my test
sheet
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips

"joromajr" wrote:


-- Question-

I need a formula for adding back value of a cell?

Let us say...

B1 c1 d1 e1
f1 g1 h1
Inv. Amt. Check1# Chk1 Amt. Chk1Status Check2# Chk2 Amt. Chk2Status

Under I1 is the balance. When the word "bounce" is encoded under
"Chkstatus" the check amout should add back to balance.

Please help need it badly.
joromajr

--

Dave Peterson

Formula for adding back value?
 

Hmmm, the first time I wrote that without the --, I got a #value error, but I
guess it just required the (E3="bounce") be encased in parens
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dave Peterson" wrote:

In this case, the -- aren't necessary. Doing arithmetic (including multiplying)
will coerce those true/false to 1/0's, too.

=B3-SUM(D3,G3)+I2+(D3*(E3="bounce"))+(G3*(H3="bounce") )

M Kan wrote:

Here's a formula that works

B3-SUM(D3,G3)+I2+(D3*--(E3="bounce"))+(G3*--(H3="bounce"))

the -- converts the logical test of E3="bounce" into a 1 or a 0

I2 is the beginning balance. Let me know if you want me to send you my test
sheet
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips

"joromajr" wrote:


-- Question-

I need a formula for adding back value of a cell?

Let us say...

B1 c1 d1 e1
f1 g1 h1
Inv. Amt. Check1# Chk1 Amt. Chk1Status Check2# Chk2 Amt. Chk2Status

Under I1 is the balance. When the word "bounce" is encoded under
"Chkstatus" the check amout should add back to balance.

Please help need it badly.
joromajr

--

Dave Peterson

Formula for adding back value?
 

-- Please send me your test sheet
joromajr


"M Kan" wrote:

Here's a formula that works

B3-SUM(D3,G3)+I2+(D3*--(E3="bounce"))+(G3*--(H3="bounce"))

the -- converts the logical test of E3="bounce" into a 1 or a 0

I2 is the beginning balance. Let me know if you want me to send you my test
sheet
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"joromajr" wrote:


-- Question-

I need a formula for adding back value of a cell?

Let us say...

B1 c1 d1 e1
f1 g1 h1
Inv. Amt. Check1# Chk1 Amt. Chk1Status Check2# Chk2 Amt. Chk2Status

Under I1 is the balance. When the word "bounce" is encoded under
"Chkstatus" the check amout should add back to balance.

Please help need it badly.
joromajr

Formula for adding back value?
 

-- Thank you, it work just fine
joromajr


"M Kan" wrote:

Here's a formula that works

B3-SUM(D3,G3)+I2+(D3*--(E3="bounce"))+(G3*--(H3="bounce"))

the -- converts the logical test of E3="bounce" into a 1 or a 0

I2 is the beginning balance. Let me know if you want me to send you my test
sheet
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"joromajr" wrote:


-- Question-

I need a formula for adding back value of a cell?

Let us say...

B1 c1 d1 e1
f1 g1 h1
Inv. Amt. Check1# Chk1 Amt. Chk1Status Check2# Chk2 Amt. Chk2Status

Under I1 is the balance. When the word "bounce" is encoded under
"Chkstatus" the check amout should add back to balance.

Please help need it badly.
joromajr