| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy back macro/roll back | Excel Worksheet Functions | |||
| Excel 2002...adding back gridlines to imported file | New Users to Excel | |||
| The Formula Bar disappeared. How can I get it back again? | Excel Discussion (Misc queries) | |||
| I need to go back to school! Help w/ formula please.. | Excel Discussion (Misc queries) | |||
| How do I set upa formula to refer back to that box to get the sum. | Excel Worksheet Functions | |||
Linear Mode
